| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Thanks
You're welcome, Phil.
LeoH "Phil Hageman" skrev i en meddelelse ... Thanks, Leo - it works fine. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I write a formula with absolute cell references | New Users to Excel | |||
| Absolute cell reference will not remain absolute. | Excel Worksheet Functions | |||
| Copying formula from one cell to another with absolute value in it | Excel Discussion (Misc queries) | |||
| Right click on cell to set formula reference as absolute | Excel Discussion (Misc queries) | |||
| Absolute References in cell formula | Excel Worksheet Functions | |||
![Phil Hageman[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
