Problem Changing Defined Name Scope in Excel 2007

[Cyberdude]

I’ve been having trouble changing the scope (worksheet or workbook) of my MANY defined names. I’m not sure what determines the scope when I create the defined name.

If it gets a scope of worksheet, then I have to somehow change it to workbook, and it isn’t clear how to do that.

I read help and it answered all questions except how to change the scope of an existing name. Suggestions anyone?
Sid

[Bob Phillips]

Sid,

Get hold of Jan Karel Pieterse's NameManager, http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp, it will help manage names far better than anything in Excel.

[SamT]

If, when you made the Name, you used SheetName! as the prefix, then it was a Sheet scoped Name. For example; Sheet1!myName.

If you then used =myName in a cell on that sheet, you were good. On another Sheet, you would have to use the complete name, ie. =Sheet1!myName.

If you had made a Book Scoped Name, you would have merely left the Sheet Name prefix off the front, ie. myName. Then you could have used that name on any sheet by merely using =myName.

Even in another workbook, you can refer to either type of Name by =[WorkbookName]Sheet1!myName.

As you can see the different naming conventions are not about Scope since any name can be used anywhere. Except that if you use the same name on two sheets, you must prefix one of them with its sheet name. Better to always prefix all duplicate names with the prefix.

Adding a non prefixed dupicate name to a book with another nonprefixed name will delete the first one.

I prefer to think of how qualified, (in the DOS sense,) a name is, rather than its Scope, (in a VBA sense.)

An "unqualified" name can be used "as is" anywhere in the book, but a qualified name can only be used on its own sheet without qualifying it. Any name can be used anywhere if you qualify it. But, in some cases you'll see Excel make a minor change to your speeling.

If you did in fact prefix or "qualify" the names that are now giving you problems, the only way to remove the prefix is to delete the name and make a new one.

You can tell which is which because qualified names will have the Sheet name next to them in the ListBox part of the Defined Names Control when their sheet is the active sheet. Unqualified names will show in the Control when any sheet is active.