Col to Rows Formula?

[Ken]

Excel2003 ...

WorkSheet 1 ... Col Range A2:A10 (my range larger, but this will do)

contains "Text" (numbers which may have leading 0's fomatted as Text)

WorkSheet 2 ... Row Range A2:I2

I wish Formula in WS2 Row Range to = WS1 Col Range? I can do this, but not
looking to do it 1 cell @ a time ... Is there an easier way?

ie:

WS2 Cell A2 = WS1 Cell A2
WS2 Cell B2 = WS1 Cell A3
WS2 Cell C2 = WS1 Cell A4
etc.

Thanks ... Kha

 

[FSt1]

hi
why not copy the data and paste special transposed.
on the menu bar...
edit>paste special>check transpose>ok

or do you just have to have a formula. i don't think you can do it with a
single formula.

Regards
FSt1

 

[JP]

Easy way:

Copy A2:A10, go to WS2, right-click A2, choose 'Paste Special' and
click the 'Transpose button'

Hard way:

Count the number of cells you want to copy to WS2. For example, 9
cells (A2:A10). Go to WS2, highlight 9 cells (i.e. A2:I2), type
"=TRANSPOSE(Sheet1Name!A2:A10)" and press Ctrl-Shift-Enter. Or simply
highlight A2:I2 on WS2, type "=TRANSPOSE(" and then switch to WS1 and
highlight A2:A10, then finish the formula with a ")" and press Ctrl-
Shift-Enter. Either way, you have to highlight the same number of
cells as is in your original range.


HTH,
JP

 

[T. Valko]

Enter this formula on Sheet2 A2 and copy across as needed:

=INDEX(Sheet1!$A2:$A10,COLUMNS($A2:A2))

 

[Tom Hutchins]

If you want formulas on WS2, rather than a one-time copy & transpose, enter
the following formula in A2 of WS2 and copy to the right as needed:

=INDIRECT("WS1!"&ADDRESS(COLUMN()+1,1))

Replace WS1 in the formula with the actual name of the WS1 sheet.

Hope this helps,

Hutch

 

[Ken]

Perfect ... Thanks for supporting these boards ... Kha

 

[T. Valko]

You're welcome. Thanks for the feedback!