Exc 97; named range recognized in one sheet but not in other

A

Ann Scharpf

I am using Excel 97. I'm having a problem I have never
seen before. Looked in help etc to no avail ...

I have two sheets: FY02 and FY03. On each sheet, I have
a single named range FISC02 (on FY02) and FISC03 (on FY03).

When I have FY02 active on the screen, both named ranges
are valid. (They both show in the pull-down list for the
cell address box.) When I have FY03 active, the only
named range that appears in the pull-down box is FISC03.
This prevents me from using FISC02 in a VLOOKUP function,
as I need to do.

I know I can hard-code the cell address range in my
VLOOKUP function but I am mystified. I've never seen a
named range show up on one sheet but be unavailable on
another sheet in the same workbook.

Can anyone give me an idea of what I could've done to
cause this? Thanks for any help you can give me.

Ann Scharpf
 
J

Jack Schitt

Is it possible that FISC03 is a globally defined named range whilst FISC02
is a name defined locally to FY02 worksheet?
To test this, select FY02 worksheet then from the menu bar
Insert/Names/Define.
Both FISC02 and FISC03 should be visible, but to the right of FISC02 you may
see the name of the worksheet FY02, whilst it would be blank to the right of
FISC03 name.

Just a possibility.
 
J

Jack Schitt

BTW, assuming that my guess is correct, I think that you should still be
able to refer to FISC02 in a VLOOKUP function in another sheet: you simply
have to refer to it by its full name, ie
FYO2!FISC02
not just
FISC02
 
A

Ann Scharpf

Hello, Jack!

Well, your suggestion gets me pointed in the right
direction. When I go into Insert > Name > Define in the
two sheets, I do see different text in the dialog box.

In FY02, I see both range names with NOTHING to the right.

In FY03, I see FISC02 with 'FY03' to the right. (This is
the name that does NOT work in the FY03 sheet.) FISC03
appears without any text to the right.

I have never used a "locally defined" name before. All my
names have always been globally available in the
workbook. I am trying to figure out how I could've
inadvertently done this. How do you create a local vs. a
global name?

Ann
 
J

Jack Schitt

I may be mistaken but it is possible that you have defined FISC02 twice in
the workbook, once as a global name and once as a name local to FY03 sheet.

It is generally bad news to have duplicate global and local names in a
workbook, unless you really meant to do it. You may want to delete the
FISC02 names and redefine it again.

When you create a name for a range, if you precede the name with the
worksheet name then it will define it locally. Thus, when defining a new
name, if you enter

Sheet1!MyName
refers to
Sheet1!$A$1

or
'Sheet 1'!MyName
refers to
Sheet1!$A$1

then it will be locally defined. (Note the single quotes where the worksheet
name contains spaces)

You can still (usually) refer to a local name from other worksheets by
specifying the full name including worksheet name as prefix (there are
exceptions, such as in conditional formatting and data validation by named
drop-down list, when you cannot access a name local to another sheet).

If you delete a worksheet that has a local name attached to it, then it will
not automatically delete the local names. Instead the names become global
names, typically referring to #REF! references (the original references
having been deleted). It is often a good idea to delete names local to a
sheet that you plan to delete, before deleting the sheet (can do this by a
macro if regular event).

If you plan to copy worksheets that contain names that refer to ranges
exclusive to that sheet then it is usually a good idea to ensure that the
names are locally defined, but it all depends what you are doing with them.

I recommend installing Jan Karel Pieterse's "Name Manager" add-in, available
from http://www.bmsltd.ie/MVP/MVPPage.asp
to help you manage these things.
 
A

Ann Scharpf

Wow! I swear I never did this! I was copying things
between sheets and I think I did create the range name
twice but I did not precede with the sheet name. I will
have to try to reproduce and see what I did wrong.

Thanks a lot. I never realized that there were global and
local range names.

I also am bookmarking the website you mentioned. Thanks
for everything!

Ann
 
J

Jack Schitt

If you have a global name that refers to a range in a worksheet and you then
create a copy of that worksheet (Edit/Copy Sheet) then Excel will create a
copy of the name and the newly created name will be created local to the new
sheet.

This is why I normally prefer to use locally defined names in preference to
global names, unless there is a particular reason for global (ie named range
in dropdown list). But if I am using global lists, then I have to be
careful about the effect of duplicating a sheet to which a global name
refers.
 
J

Jack Schitt

BTW, just a thought, but if you are working from a template worksheet within
the workbook, ie FY02, and duplicating the template to create a new year, ie
FY03, then say you created a local name FY02!FISC before duplicating it,
then duplicate FY02 to create FY03 worksheet, you will find that you already
have a local name FY03!FISC, in addition to the original local name
FY02!FISC.

Then, if you are in any particular worksheet that has this local name, you
can refer to the name local to that sheet (ie in your VLOOKUP formula) just
by referring to its abbreviated name (ie without the sheet prefix).
Entering it long-hand, ie by reference to the sheet name, will also work and
you can refer to names local to other worksheets but with the same suffix
name, provided that you specify the sheet.

A point that I find mildly irritating is: If you refer to a local name it
would have been nice if specifying the sheet prefix anchored it when copying
the sheet, ie:
If
cell name Sheet1!MyName refers to Sheet1!$A$1
cell Sheet1!A2 contains formula =MyName ...which translates to
Sheet1!MyName, ie Sheet1!$A$1
cell Sheet1!A3 contains formula =Sheet1!MyName

then it would have been nice if duplicating sheet1 and calling it sheet2
would give rise to:
cell name Sheet2!MyName refers to Sheet2!$A$1 (it does this)
cell Sheet2!A2 contains formula =MyName ...which translates to
Sheet2!MyName, ie Sheet2!$A$1 (it does this)
cell Sheet2!A3 contains formula =Sheet1!MyName (it does NOT do this, but
instead contains =Sheet2!MyName)

In other words, provided that there is no duplication of global and local
names, specifying the local sheet name as a prefix to the range name in a
formula within the same sheet is a superfluous waste of time. It would have
been nice if the program made that distinction. I have in the past wished
for it in practice.
 
Top