Read data from a Row to a Column

[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.

 

[Ken G.]

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,)

 

[T. Valko]

=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))

 

[Max]

Think the expression that you posted:

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

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
---

 

[Ken G.]

Actually I needed both - row to column and column to row, so thanks Biff and
Max. Problem solved!

 

[Max]

Welcome, Ken
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik