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

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

On Wed, 2 Jul 2008 06:40:05 -0700, jackle298
<(E-Mail Removed)> 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
<(E-Mail Removed)> 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.