Solved: The Agony of Defined Name Overrides

[Cyberdude]

I have two nearly identical workbooks and they each contain mostly the same Defined Names. If I copy a worksheet from Workbook A to Workbook B, then many of the defined names in Workbook A will be defined for the copied worksheet in Workbook B as an override to identically defined names in Workbook B. I cannot use the copied worksheet until I manually go through and delete all the override defined names that have been defined for it. I?ve been doing this annoying task once a month for three worksheets for literally years.

I?ve never really understood what dictates that a particular defined name has an override name defined for it. A lot of the names occur in sequences of 12 (Security1, Security2, Security3,?), but not necessarily all the names in a sequence are overridden (but usually they all are). I have a hazy idea of why this is done by Excel, but when you have several hundred names to remove each month, it gets to be a dreaded chore.

Is there a way to prevent Excel from making the override definitions when a sheet is copied? (Groan)

[Zack Barresse]

Not that I know of Sid. This would be a perfect example where we should welcome the wide world of programming into Excel.

A procedure should be able to fix this for you, given your logic of course.

[Cyberdude]

Yeah, your right, Zack. I guess I should start yet another background job looking into how to cope with the problem programmatically. (Phffft!)

[Zack Barresse]

MS MVP Jan Karel Pieterse has a NameManager addin here: http://www.oaltd.co.uk/MVP/Default.htm .. which may be of some help for you.

[skulakowski]

Jan Karel Pieterse's add-in is also at http://www.decisionmodels.com/downloads.htm. I find it very, very handy.

BTW, I use the Memory Limits and Memory Leaks pages (http://www.decisionmodels.com/memlimitsc.htm and http://www.decisionmodels.com/memlimitsd.htm) to explain to my users that when I say they have to quit out of Excel and start again, I'm not always making it up.

[Cyberdude]

Extremely interesting references, guys. Thanks for the assist.

[Cyberdude]

For what it's worth I emailed Jan Pieterse (the defined name guru), and he just replied with the following:

Hi Sid,

Is there a way to prevent Excel from making the override definitions
> when a sheet is copied?

No, there isn't. But my Name Manager (www.jkp-ads.com/officemarketplacenm-en.htm) will greatly ease the process of getting rid of them. The "override" names are in fact names local to the just copied worksheet (hence the name of the sheet behind them). So if you only use global names normally, just filter the list of names to show only local names, select them all and click the delete button (the one with the red X).
Regards,
Jan Karel Pieterse
JKP Application Development Services

So there you have it, folks.