jonofisher
Board Regular
- Joined
- May 25, 2005
- Messages
- 51
Hello All,
I thought what i am trying to do would be simple however this has left me surprised and frustrated, hence i am hoping someone else has experienced this issue before and can assist.
I am trying to apply named ranges to existing formulas.
Basically, the spreadsheet i inherited was poorly planned and did not use names. I am therefore defining a number of names and then want to update the existing formulas across the workbook with them - to make formulas easier to audit.
Supposedly this should be a simple process by going to "Define Names" and then "Apply names" (in excel 2007). However, whenever i do this, i get the error "Microsoft office excel cannot find any references to replace".
For example:
On the assumptions sheet (called "2B. Key Assumptions") i have defined a cell c296 as "lease_switch". This is defined with workbook scope (not worksheet).
On another worksheet ("Financing") there a number of cells with a long formula such as:
=IF('2B. Key assumptions'!$C$296=1,IF('2B. Key assumptions'!$C$297='10. Financing'!HO8,('10. Financing'!HO12/SUM('10. Financing'!HO80,'10. Financing'!HO12))*'2B. Key assumptions'!$C$298,0),0)
I would like to be able to replace the formula so it will become (IF(lease_switch=1... etc)
I would have expected this to have been a fairly simple process. Browsing google results it seems people have had this error before, but i can't seem to find a clear answer to it.
I know i could use the find and replace function, but then i need to be very careful about which formulas are replaced (ie in my example, replacing '2B. Key assumptions'!$C$296 would also replace a reference to '2B. Key assumptions'!$C$2960 which would stuff a few things up.
Many thanks for any assistance provided.
JFish
I thought what i am trying to do would be simple however this has left me surprised and frustrated, hence i am hoping someone else has experienced this issue before and can assist.
I am trying to apply named ranges to existing formulas.
Basically, the spreadsheet i inherited was poorly planned and did not use names. I am therefore defining a number of names and then want to update the existing formulas across the workbook with them - to make formulas easier to audit.
Supposedly this should be a simple process by going to "Define Names" and then "Apply names" (in excel 2007). However, whenever i do this, i get the error "Microsoft office excel cannot find any references to replace".
For example:
On the assumptions sheet (called "2B. Key Assumptions") i have defined a cell c296 as "lease_switch". This is defined with workbook scope (not worksheet).
On another worksheet ("Financing") there a number of cells with a long formula such as:
=IF('2B. Key assumptions'!$C$296=1,IF('2B. Key assumptions'!$C$297='10. Financing'!HO8,('10. Financing'!HO12/SUM('10. Financing'!HO80,'10. Financing'!HO12))*'2B. Key assumptions'!$C$298,0),0)
I would like to be able to replace the formula so it will become (IF(lease_switch=1... etc)
I would have expected this to have been a fairly simple process. Browsing google results it seems people have had this error before, but i can't seem to find a clear answer to it.
I know i could use the find and replace function, but then i need to be very careful about which formulas are replaced (ie in my example, replacing '2B. Key assumptions'!$C$296 would also replace a reference to '2B. Key assumptions'!$C$2960 which would stuff a few things up.
Many thanks for any assistance provided.
JFish