Columns to rows

rdeters

New Member
Joined
May 6, 2002
Messages
40
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Kind of, it is the same file, but I took you're advice from the ealier one and changed the excel file/program a bit to make it less difficult in the end. The problem I am now facing seems to be somewhat easier. But I guess not, considering I haven't figured it out yet. What's your opinion on this Chris?
 
Upvote 0
My opinion is it's tricky.... just this above example is proving tricky (i'm working on it now)

(although I'm no expert by any stretch of the imagination!)

nearly there though....
 
Upvote 0
A little bit of more input: Looking at the incremental format that excel is generating when I pull down the cells, the various groups go: 3,10,17,24,31,etc. (I of course want 3,4,5,6,7,etc,) The mathmatical formula turns out to be y=((x-3)/7)+3, where x is the row that excel is inputing into the cell when dragged down. In the previos reply that you indicated on the other problem, you said that you could indicate an indirect input for the row number, is this possible with this mathematical formula that I have supplied? I hope this explanation is clear.
 
Upvote 0
purely for this example....

try this in your other sheet, cell A1 :

=INDIRECT("'Instrument Tags'!"&CHAR(SUBSTITUTE(MOD(ROW(),7),0,7)+83)&ROUNDUP((ROW()/7),0)+2)

and just copy down as far as is needed

If anyone can follow what I've done here, I'd appreciate it if you could suggest an easier or tidier way : the logic of transposing coupled with then dropping down a row after each 7th record was a bit of a mind-twister.... hence the ugly formula

but it works...so this should get you started



_________________
Hope this helps,
Chris
uk.gif


be careful to ensure the single quotes around ....instrument tags.... it's sometimes easy to miss them on this board
This message was edited by Chris Davison on 2002-05-08 14:29

just to test fully : this formula in A1 copied down to row 3486, produces the follwoing result in cell 3486 :

"Z500"

which is the 7th column (column Z) on row 500
from your Instruments Tags sheet
This message was edited by Chris Davison on 2002-05-08 14:44
 
Upvote 0
The mathmatical formula turns out to be y=((x-3)/7)+3

I like your thought processes..... this is exactly the same sort of logic I've been applying to something similar but of a different problem scenario....

[a tip, if you're used to looking at this sort of logic-solving relating to rows : use excel's fractional formats, rather than decimals : it'll maybe help highlight an error in terms of row numbers 11 and 17 as 11/17 rather than an error of 0.647058824 which means nothing !]

Anyhow, hope the above helps, although I suspect it may not be the end of your problem

:wink:
 
Upvote 0
Chris, you are the man. Thank you ever so much. I have no idea how you figured this out, but that is pretty good! Thanks again.
 
Upvote 0
no worries...

I'll run through it if you want, step by step

I suspect you *will* follow it given your own input of that mathmatical formula you suggested....

give me a shout and I'll post the individual parts, although it may not be today as it's 11pm here and to be honest this single post has exhausted me !
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top