Transpose Reletive Cell References?

[Conan Kelly]

Hello all,

Is there any way that I can get relative cell references to transpose
when I fill across?

For example:

cell A1's formula on Sheet 1 would be '=Sheet2!A1'. Now when I fill
this across, I would like B1's formula on Sheet 1 to be equivalent to
'=Sheet2!A2'. Would INDIRECT work for this?

Thanks for any help that anyone can provide,

Conan Kelly

 

[Max]

3 alternatives which could be used to get this up ..

In Sheet1, placed in any starting cell,
say, in B2, then B2 copied across:

=INDEX(Sheet2!$A:$A,COLUMN(A1))
=INDIRECT("Sheet2!A"&COLUMN(A1))
=OFFSET(Sheet2!$A$1,COLUMN(A1)-1,)