Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 75
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 296
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 75
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I Need a formula to evaluate a cell with + or - values Bob in Oklahoma Excel Worksheet Functions 6 October 31st 05 03:41 PM
Change formula to Indirect Ronbo Excel Worksheet Functions 3 October 10th 05 07:33 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
Cannot change a formula Richard F Excel Discussion (Misc queries) 1 January 18th 05 02:57 AM
how do I make a formula NOT change when the data range is moved? Alida Andrews Excel Discussion (Misc queries) 2 January 6th 05 10:02 PM


All times are GMT +1. The time now is 11:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"