Absolute cell formula
 

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

Absolute cell formula
 

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" wrote in message
...

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

Absolute cell formula
 

Phil

=INDIRECT("C1")

will do the job.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.


"Phil Hageman" skrev i en meddelelse
...

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

Absolute cell formula
 

=Indirect("C3")

--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...

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

Thanks
 

Thanks, Tom - works fine...
-----Original Message-----
=Indirect("C3")

--
Regards,
Tom Ogilvy

"Phil Hageman"
wrote in message
...

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


.

Thanks
 

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"
wrote in message
...

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


.

Thanks
 

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"
skrev i en meddelelse
...

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


.

Thanks
 

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

--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
Thanks, Tom - works fine...
-----Original Message-----
=Indirect("C3")

--
Regards,
Tom Ogilvy

"Phil Hageman"
wrote in message
...

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


.

Thanks
 

You're welcome, Phil.

LeoH


"Phil Hageman" skrev i en meddelelse
...
Thanks, Leo - it works fine.