Transposing

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Ok my second question but I'm a excel newbie...

I have a 'Config' table on one worksheet that has a column of items (about
a 1000 long) going down the page.

On another sheet I need to populate a row with entries from the columns. In
other words
source A1, destination A1
Source A2, destination B1
source A3, destination C1
etc

how can I do this If I want to have relative referencing I.e. the formula in
the 2nd cell in the destination sheet is '=Source!A2

please tell me there is a way without manually tying A1,A2,A3 etc

in pasting terms I can do this selecting past and transpose, but I want to
do it by setting up a relative reference and dragging the little black cross
in the bottom right hand corner

any ideas

Thanks

tom
 
Hi Tom
on your second sheet enter the following in A1
=INDIRECT("'source'!A" & COLUMN())
copy to the right
note the multiple apostrophes at the beginning (" and ')

Frank
 
Another way, is that you can select say A1:IU1, type =TRANSPOSE( then select
A1:A255 on the source sheet, put in the last parenthesis ) and then array
enter the data using CTRL+SHIFT+ENTER.

Note, this is only 255 columns worth, and you will not be able to get all 1000
rows of data transposed in a single row, but will have to split it across 4
rows, so for example you may have to do

Select A1:IQ1 and type
=TRANSPOSE( select A1:A250 blah blah blah

Select A2:IQ2 and type
=TRANSPOSE( select A251:A500 blah blah blah

Select A3:IQ3 and type
=TRANSPOSE( select A501:A750 blah blah blah

Select A4:IQ4 and type
=TRANSPOSE( select A751:A1000 blah blah blah

Array formulas can be memory intensive though, so you may want to adoipt a
similar approach using Frank's formula.
 
Cool,

It works and it looks like INDIRECT is functionally similar to a C
pointer... very useful!!

thanks

tom
 
Back
Top