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.

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


"jackle298" wrote:

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.

One method.

Select the cells in the column, say A1:A20

EditReplace

What: =

With: ^^

Replace all.

CopyPaste SpecialTransposeOKEsc.

EditReplace to reverse.

If you want a macro for this post back.


Gord Dibben MS Excel MVP

On Wed, 2 Jul 2008 06:40:05 -0700, jackle298
wrote:

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.

Example of this Gary

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


Gord Dibben MS Excel MVP

On Wed, 2 Jul 2008 13:33:04 -0700, Gary Brown
wrote:

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.