Absolute cell formula

[Phil Hageman]

Case One Case Two
A B C D A B C D
1 =C1 3 1 =C1 5 3

In Case One, the value for B1 is 3. When a new column C
is inserted (Case Two), the formula in B1 is automatically
changed to =D1, and the value remains 3. Is there a way
to write the formula so it retains the intent of =C1 after
the new column is inserted - such that the value in B1
would now equal 5?
=$C$1 doesn't work - it too changes to =$D$1.
Thanks,
Phil

 

[Bob Phillips]

Phil,

Try

=INDIRECT("C1")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Leo Heuser]

Phil

=INDIRECT("C1")

will do the job.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

 

[Tom Ogilvy]

=Indirect("C3")

 

[Phil Hageman]

Thanks, Tom - works fine...

-----Original Message-----
=Indirect("C3")

--
Regards,
Tom Ogilvy




.

 

[Phil Hageman]

Thanks, Bob - it works!

-----Original Message-----
Phil,

Try

=INDIRECT("C1")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.

 

[Phil Hageman]

Thanks, Leo - it works fine.

-----Original Message-----
Phil

=INDIRECT("C1")

will do the job.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.


"Phil Hageman" <[email protected]> skrev i en meddelelse



.

 

[Tom Ogilvy]

Guess everyone knew but you <g>, and I was late to the party.

 

[Leo Heuser]

You're welcome, Phil.

LeoH