Columns to rows

[rdeters]

What I am trying to do is take a 7 column by 500 row grid of information from a worksheet and populate it into a grid that is 1 column by 3500 rows on another worksheet. In other words 7 columns of data will become 1 column with 7 rows. I can not use the copy & paste function, because the original grid is initially blank. While info is being entered into the grid, I want it to be displayed on the next worksheet in the format I described. I have tried the transpose function and it does not seem to work for me, I may be doing something wrong though. Any help would be appreciative.

Here is an example of the formulas:
cell A1 : ='Instrument Tags'!T3
cell A2 : ='Instrument Tags'!U3
cell A3 : ='Instrument Tags'!V3
cell A4 : ='Instrument Tags'!W3
cell A5 : ='Instrument Tags'!X3
cell A6 : ='Instrument Tags'!Y3
cell A7 : ='Instrument Tags'!Z3

When I try to carry these over to the next 7 rows, here is what the formulas look like:
cell A8 : ='Instrument Tags'!T10
cell A9 : ='Instrument Tags'!U10
cell A10 : ='Instrument Tags'!V10
cell A11 : ='Instrument Tags'!W10
cell A12 : ='Instrument Tags'!X10
cell A13 : ='Instrument Tags'!Y10
cell A14 : ='Instrument Tags'!Z10

But this is what I am assuming it sould look like:
cell A8 : ='Instrument Tags'!T4
cell A9 : ='Instrument Tags'!U4
cell A10 : ='Instrument Tags'!V4
cell A11 : ='Instrument Tags'!W4
cell A12 : ='Instrument Tags'!X4
cell A13 : ='Instrument Tags'!Y4
cell A14 : ='Instrument Tags'!Z4

 

[eliW]

Hi Chris and rdeters,

Chris, I was really impressed by your great effort in solving RDETERS question, which you did brilliantly. Yet you yourself asked for more suggestions to an “easier or tidier way“…I think I’ve got one, which uses the INDIRECT formula in a simple way.

1) In the other worksheet (sheet2) choose any column, say “D” and enter in

D1 -> "Instrument Tags!T3"
D2 -> "Instrument Tags!U3"
D3 -> "Instrument Tags!V3"
D4 -> "Instrument Tags!W3"
D5 -> "Instrument Tags!X3"
D6 -> "Instrument Tags!Y3"
D7 -> "Instrument Tags!Z3"

2) Now select D1:D7 and drag all the way down to D3500 in one stroke, the row numbers will grow by 1 every 7 rows

3) Put in cell A1: =INDIRECT(D1) and copy it down 3500 rows.

I think that this is a simple approach, which utilizes the Excel property to raise numbers in fixed increments and repeat sequences when dragging them from cell to cell.

I’ll appreciate your opinion about it.

Regards,

Eli
This message was edited by eliW on 2002-05-09 11:20

 

[Chris Davison]

Eli,

spot on.... much less complicated formulae too

I just noticed where I was going wrong offline in your example.... your initial sheet names aren't actually links, they are just manually typed in, hence the autofill incrementation exploitation

works well

 

[eliW]

Yes Chris, that's what I meant.
I wasn't clear enough in my first reply - Look at the eddited one, I hope it's more clear now.
Regards,
Eli

 

[Chris Davison]

I'll be honest Eli...

I never knew you could autofill like that, with ranges...

I do singles all the time just never made the connection with ranges

thanks ! I owe ya

Chris