Col to Rows Formula?

K

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
 
F

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
 
J

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

T. Valko

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

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top