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

"Ken G." wrote:

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.

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


--
Biff
Microsoft Excel MVP


"Ken G." wrote in message
...
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,)

"Ken G." wrote:

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.

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

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

"Max" wrote:

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

Welcome, Ken
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik
---
"Ken G." wrote in message
...
Actually I needed both - row to column and column to row, so thanks Biff
and
Max. Problem solved!