Col to Rows Formula?
 

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

Col to Rows Formula?
 

hi
why not copy the data and paste special transposed.
on the menu bar...
editpaste specialcheck transposeok

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

Regards
FSt1

"Ken" wrote:

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

Col to Rows Formula?
 

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



On Feb 12, 1:22*pm, Ken wrote:
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

Col to Rows Formula?
 

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

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


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
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

Col to Rows Formula?
 

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" wrote:

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

Col to Rows Formula?
 

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

"T. Valko" wrote:

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

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


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
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

Col to Rows Formula?
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Perfect ... Thanks for supporting these boards ... Kha

"T. Valko" wrote:

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

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


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
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