Copy/Past/Transpose Formulas

[jackle298]

Is there any way to keep the formulas referencing the original cells when
transposing a column to a row. It always seems to pick up the transposed row
reference, i.e. A1-G1 vs. the original column reference A1-A7.

 

[Gary Brown]

Seems I read somewhere quite some time ago that using the Offset function
instead of a reference to the actual cell will solve that problem.
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown

 

[Gord Dibben]

One method.

Select the cells in the column, say A1:A20

Edit>Replace

What: =

With: ^^

Replace all.

Copy>Paste Special>Transpose>OK>Esc.

Edit>Replace to reverse.

If you want a macro for this post back.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Example of this Gary

=OFFSET($A$1,COLUMN(A1)-1,) entered in B1 and copied across.


Gord Dibben MS Excel MVP