Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert-Name-Define How to change formula after-the-fact?
I love using dynamically named ranges to keep up with expanding spreadsheets,
but when I want to make a change to a named range (for example, expand the OFFSET function to include 2 columns instead of 1, or to start at a different reference cell), I get one (or several) of the following problems: 1) References to the edited named range stop working, although the name is the same (the worksheet that is listed to the right side of the top box disappears when this happens) 2) Instead of updating the original name, it makes a copy and now I have 2 "names" (and my charts, etc. keep referring to the original). Again, somehow the worksheet association disappears. 3) Occasionally, my entire OFFSET formula disappears and is replaced by =$B$12 (or whatever cell was selected when I did Insert--Name--Define). This happens AFTER I type in the OFFSET function and hit OK (so I don't see that the wrong formula is there until I go to troubleshoot and find it. Does anyone else have trouble changing existing named ranges? Any suggestions for how to make them behave better? Thank you, Heidi |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert-Name-Define How to change formula after-the-fact?
On Wed, 23 Aug 2006 11:51:01 -0700, Heidi
wrote: I love using dynamically named ranges to keep up with expanding spreadsheets, but when I want to make a change to a named range (for example, expand the OFFSET function to include 2 columns instead of 1, or to start at a different reference cell), I get one (or several) of the following problems: 1) References to the edited named range stop working, although the name is the same (the worksheet that is listed to the right side of the top box disappears when this happens) 2) Instead of updating the original name, it makes a copy and now I have 2 "names" (and my charts, etc. keep referring to the original). Again, somehow the worksheet association disappears. 3) Occasionally, my entire OFFSET formula disappears and is replaced by =$B$12 (or whatever cell was selected when I did Insert--Name--Define). This happens AFTER I type in the OFFSET function and hit OK (so I don't see that the wrong formula is there until I go to troubleshoot and find it. Does anyone else have trouble changing existing named ranges? Any suggestions for how to make them behave better? Thank you, Heidi Yes I too find the standard Insert Name Define dialog box difficult to work with. Can I recommend the admirable 'Name Manager' add in from http://www.decisionmodels.com/downloads.htm I find it so much more user friendly, and if you need to manage names on a regular basis, there are many useful features to assist you. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert-Name-Define How to change formula after-the-fact?
Thanks! That seems like a great tool.
"Richard Buttrey" wrote: On Wed, 23 Aug 2006 11:51:01 -0700, Heidi wrote: I love using dynamically named ranges to keep up with expanding spreadsheets, but when I want to make a change to a named range (for example, expand the OFFSET function to include 2 columns instead of 1, or to start at a different reference cell), I get one (or several) of the following problems: 1) References to the edited named range stop working, although the name is the same (the worksheet that is listed to the right side of the top box disappears when this happens) 2) Instead of updating the original name, it makes a copy and now I have 2 "names" (and my charts, etc. keep referring to the original). Again, somehow the worksheet association disappears. 3) Occasionally, my entire OFFSET formula disappears and is replaced by =$B$12 (or whatever cell was selected when I did Insert--Name--Define). This happens AFTER I type in the OFFSET function and hit OK (so I don't see that the wrong formula is there until I go to troubleshoot and find it. Does anyone else have trouble changing existing named ranges? Any suggestions for how to make them behave better? Thank you, Heidi Yes I too find the standard Insert Name Define dialog box difficult to work with. Can I recommend the admirable 'Name Manager' add in from http://www.decisionmodels.com/downloads.htm I find it so much more user friendly, and if you need to manage names on a regular basis, there are many useful features to assist you. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
Change formula to Indirect | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Cannot change a formula | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) |