Read data from a Row to a Column

  • Thread starter Thread starter Ken G.
  • Start date Start date
K

Ken G.

I have data in a row, say A1:Z1 and I need to create a formula so that I can
copy down From say A2:A27. I don't want to copy and paste transpose as the
data is volatile. I need a formula for cell A2 that I can then copy down
column A to read the data from row 1 without having to manually change the
column reference for each row.
 
Sorry, I realised I've already asked this question and was given a good
answer that worked for me ...

=OFFSET(Sheetname!$A$1,COLUMNS($A:A)-1,)
 
=OFFSET(Sheetname!$A$1,COLUMNS($A:A)-1,)

That'll work for a vertical to horizontal flip. I thought you wanted the
reverse, horizontal to vertical:

Entered in A2 and copied down as needed:

=INDEX(Sheetname!A$1:Z$1,ROWS(A$2:A2))
 
Think the expression that you posted:
=OFFSET(Sheetname!$A$1,COLUMNS($A:A)-1,)
Click to expand...
is for copying across, to transpose dynamically from a column data range

To do it the other way around (your objective here),
ie copy down to transpose dynamically from a row data range
use this in the start cell, copy down:
=OFFSET(Sheetname!$A$1,,ROWS($1:1)-1)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik
---
 
Actually I needed both - row to column and column to row, so thanks Biff and
Max. Problem solved!
 
Back
Top