multi level range name

[Andre Croteau]

Hello,

How does one get multi level range names that can be used in more than one
sheet of a workbook? Is it automatic when we create a range name?

In the INSERT-NAME-DEFINE command, I don't remember how to get a range name
to be present in each of the sheets of my workbook.
Sometimes, I notice in the DEFINE NAME WINDOW that a sheet name appears to
the right of a range name, and it works well when I change sheets, the range
name reference also switches sheets. Other times, there are no names next
to the range name, and I don't what causes the difference

I looked in my Excel reference book, and it is not very clear.
Any help would be appreciated.

André

 

[Dave Peterson]

Include the sheet name in your definition of the name.

sheet1!myname
or
'sheet 1'!myname

 

[Andre Croteau]

Hello Gord,
Thanks, this works well for my macros.

However, in doing it this way, that range name does not appear in the NAME
BOX as part of the list of range names.

I do have another range name in my file which has these caracteristics:
-is in the list in the NAME BOX
-in the DEFINE NAME window, the range name appears and has the name of the
active sheet to the right of it.
When i switch from sheet to sheet, and access the range name from the NAME
BOX, it refers to that range in the active sheets

I just don't know what I did to get that situation, and I would like to do
it again for other range names.

Any other miracle cures?

André

 

[Dave Peterson]

In a private email, Andre wrote:

Hi Dave,

I had tried that, but when I go to sheet2, and try to highlight the MYNAME
range, it goes back to sheet1.

Earlier tonight, I did have the name MYNAME in the DEFINE NAME box, and it
also had the name "sheet2" to the right of it while I was in sheet2. When I
went back to sheet1, the MYNAME range then had "sheet1" next to it. Now,
there are no reference to the right of MYNAME, and I don't know what I did
to make it disappear. How to make it reappear?

==============

Try highlighting your range on sheet2.
Then Insert|Name. (don't click on MyName at all)
Type Sheet2!myName (in the "Names in Workbook" box)

Since you selected the range first, you won't have to adjust the "refers to"
box.

Names can be local to the sheet or they can be global. If you include the
sheetname in the definition, then they'll be local to the sheet--which allows
you to use the same name on different sheets.

If you're going to do anything with range names, get a copy of an addin written
by Jan Karel Pieterse, Charles Williams and Matthew Henson. It's a really good
way to see what's happening with names.

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

(It's much easier than using the builtin Insert|Name dialog.)