Insert-Name-Define How to change formula after-the-fact?

[Guest]

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

 

[Richard Buttrey]

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
__________________________

 

[Guest]

Thanks! That seems like a great tool.

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
__________________________