Transpose cells with formulae..

[Chartist]

Hi, I have a long vertical list of values (A1, A2, A3,....) . I want to report these values horizontally, in cells B1,C1,D1,.....
The only way I know is to type =A1 in cell B1, =A2 in cell C1 and so on, which will waste several hours of my time (this situation keeps coming up repeatedly as well.) Transpose is not keeping the formulae absolute, so any ideas to do this faster?

 

[njimack]

Paste the following into B1, then copy across as far as necessary:
=OFFSET($A$1,COLUMN()-2,0)

 

[Andrew Poulsom]

If you want to preserve the exact formulas, select the cells and choose Edit|Replace What = With != to convert your formulas to text. Then Copy|Paste Special|Transpose. Finally replace != with =.

 

[Jonmo1]

Nevermind

njimack's Offset formula is way better than my Indirect


But Index is not volatile, so this may be better

=INDEX($A:$A,COLUMN(A1))

 

[njimack]

Jonmo,

I was just about to edit my post to include an alternative, using INDIRECT:
=INDIRECT("A"&COLUMN()-1)

 

[Chartist]

brilliant.... very happy, thanks all