Confused about range names

[Busy John]

I'm using Excel 2002.
I have 30 or so range names in my file. When creating these range names, I
didn't precede the name with the sheet name on any of the names. As I
understand it, this makes all the names Workbook range names.
These range names apply to ranges on several different sheets. When I view
the range names with Insert - Names - Define, with the "Utility" sheet
active, all the names that apply to the "Utility" sheet have the word
"Utility" to the right, opposite the range name. None of the other range
names show the sheet name to the right. When I select any of these
"Utility" sheet names, the "Refers to:" box shows the name of the sheet and
the range as expected..
But when I view the range names (with Insert - Names - Define) when the
"Utility" sheet is NOT active, and I select one of the "Utility" sheet
names, the "Refers to:" box shows "#REF!" and the range.
Why does this happen?
Also, when I select any of the other names, with any sheet active, none of
them display the sheet name to the right, nor have "#REF!" for the sheet
name in the "Refers to:" box.
I thought that perhaps "Utility" was an Excel key word that I shouldn't use
for a sheet name, but the problem persists when I change the name of the
"Utility" sheet.
What is happening? Thanks. John

 

[Dave Peterson]

If you see the worksheet name to the right (in that insert|Name dialog), then
those names are worksheet level names.

If you want to convert from workbook level to worksheet level (or from worksheet
level to workbook level), you could struggle with code--or you can get Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It'll make figuring out those names with errors easier, too.

 

[Busy John]

Thanks Dave, but how did those names get to be worksheet range names when I
didn't precede the names with the sheet names when I created the names
originally?
Also, if I delete all those names and recreate them, how should I do it so
that they are not worksheet range names again?
Thanks for that site. I will download that file. John

 

[Dave Peterson]

I have no idea what you did to create the worksheet level names. You may have
created the name on a worksheet, then copied that worksheet within the same
workbook.

Since you can't have two (global) names with the same name, excel will convert
them to worksheet level names on that new (copied) worksheet.

And if you download that tool, you'll find that you don't need to delete and add
them.

There's an option to convert them (both ways).