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

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
0
anonymous (74722)
9/10/2004 5:54:22 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
548 Views

Similar Articles

[PageSpeed] 28

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.

-- 
Return email address is not as DEEP as it appears
"Ann Scharpf" <anonymous@discussions.microsoft.com> wrote in message 
news:9cad01c4975f$343b10f0$a601280a@phx.gbl...
>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 


0
mind-the-gap (250)
9/10/2004 6:08:09 PM
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


-- 
Return email address is not as DEEP as it appears
"Jack Schitt" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message 
news:OQ0HkE2lEHA.1644@tk2msftngp13.phx.gbl...
> 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.
>
> -- 
> Return email address is not as DEEP as it appears
> "Ann Scharpf" <anonymous@discussions.microsoft.com> wrote in message 
> news:9cad01c4975f$343b10f0$a601280a@phx.gbl...
>>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
>
> 


0
mind-the-gap (250)
9/10/2004 6:31:52 PM
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
>-----Original Message-----
>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.
>
>-- 

0
anonymous (74722)
9/10/2004 6:52:31 PM
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.


-- 
Return email address is not as DEEP as it appears
"Ann Scharpf" <anonymous@discussions.microsoft.com> wrote in message 
news:01d501c49767$53c101c0$a301280a@phx.gbl...
> 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
>>-----Original Message-----
>>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.
>>
>>-- 
> 


0
mind-the-gap (250)
9/10/2004 7:09:53 PM
Typo

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

should of course be

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



-- 
Return email address is not as DEEP as it appears
"Jack Schitt" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message 
news:OqbcDn2lEHA.3544@TK2MSFTNGP15.phx.gbl...
>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.
>
>
> -- 
> Return email address is not as DEEP as it appears
> "Ann Scharpf" <anonymous@discussions.microsoft.com> wrote in message 
> news:01d501c49767$53c101c0$a301280a@phx.gbl...
>> 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
>>>-----Original Message-----
>>>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.
>>>
>>>-- 
>>
>
> 


0
mind-the-gap (250)
9/10/2004 7:17:49 PM
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
>-----Original Message-----
>Typo
>
>>
>> or
>> 'Sheet 1'!MyName
>> refers to
>> Sheet1!$A$1
>>
>
>should of course be
>
> or
>'Sheet 1'!MyName
>refers to
>'Sheet 1'!$A$1
>
>
>
>-- 
>Return email address is not as DEEP as it appears
>"Jack Schitt" <mind-the-gap@DEEPblueyonder.co.uk> wrote 
in message 
>news:OqbcDn2lEHA.3544@TK2MSFTNGP15.phx.gbl...
>>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.
>>
>>
>> -- 
>> Return email address is not as DEEP as it appears
>> "Ann Scharpf" <anonymous@discussions.microsoft.com> 
wrote in message 
>> news:01d501c49767$53c101c0$a301280a@phx.gbl...
>>> 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
>>>>-----Original Message-----
>>>>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.
>>>>
>>>>-- 
>>>
>>
>> 
>
>
>.
>
0
anonymous (74722)
9/10/2004 7:32:07 PM
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.

-- 
Return email address is not as DEEP as it appears
"Ann Scharpf" <anonymous@discussions.microsoft.com> wrote in message 
news:013201c4976c$dc114d00$a501280a@phx.gbl...
> 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
>>-----Original Message-----
>>Typo
>>
>>>
>>> or
>>> 'Sheet 1'!MyName
>>> refers to
>>> Sheet1!$A$1
>>>
>>
>>should of course be
>>
>> or
>>'Sheet 1'!MyName
>>refers to
>>'Sheet 1'!$A$1
>>
>>
>>
>>-- 
>>Return email address is not as DEEP as it appears
>>"Jack Schitt" <mind-the-gap@DEEPblueyonder.co.uk> wrote
> in message
>>news:OqbcDn2lEHA.3544@TK2MSFTNGP15.phx.gbl...
>>>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.
>>>
>>>
>>> -- 
>>> Return email address is not as DEEP as it appears
>>> "Ann Scharpf" <anonymous@discussions.microsoft.com>
> wrote in message
>>> news:01d501c49767$53c101c0$a301280a@phx.gbl...
>>>> 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
>>>>>-----Original Message-----
>>>>>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.
>>>>>
>>>>>-- 
>>>>
>>>
>>>
>>
>>
>>.
>> 


0
mind-the-gap (250)
9/10/2004 7:40:37 PM
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.

-- 
Return email address is not as DEEP as it appears
"Ann Scharpf" <anonymous@discussions.microsoft.com> wrote in message 
news:013201c4976c$dc114d00$a501280a@phx.gbl...
> 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
>>-----Original Message-----
>>Typo
>>
>>>
>>> or
>>> 'Sheet 1'!MyName
>>> refers to
>>> Sheet1!$A$1
>>>
>>
>>should of course be
>>
>> or
>>'Sheet 1'!MyName
>>refers to
>>'Sheet 1'!$A$1
>>
>>
>>
>>-- 
>>Return email address is not as DEEP as it appears
>>"Jack Schitt" <mind-the-gap@DEEPblueyonder.co.uk> wrote
> in message
>>news:OqbcDn2lEHA.3544@TK2MSFTNGP15.phx.gbl...
>>>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.
>>>
>>>
>>> -- 
>>> Return email address is not as DEEP as it appears
>>> "Ann Scharpf" <anonymous@discussions.microsoft.com>
> wrote in message
>>> news:01d501c49767$53c101c0$a301280a@phx.gbl...
>>>> 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
>>>>>-----Original Message-----
>>>>>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.
>>>>>
>>>>>-- 
>>>>
>>>
>>>
>>
>>
>>.
>> 


0
mind-the-gap (250)
9/11/2004 9:39:01 AM
Reply:

Similar Artilces:

How to total three separate reports into one report:
I have three separate reports that are working great! Management would like to see the total lines of each report on one page without the detail that feeds to the summary of each column. How do I compile the totals of these three separate reports into one page w/just a title and total of the categories that they would like to see from each report? Thank you. Example: Report Name # # # Category 1 4 5 6 Category 2 2 8 1 Category 3 9 7 4 Evelyn Williams wrote: >I have three separate reports that are working great! Management would like >to see the tota...

Keep column names at top of workbook
How can I have my column names stay at the top of the Excel workbook when I scroll down? Select cell A2. From menu Windows>Freeze Panes If this post helps click Yes --------------- Jacob Skaria "Isabel" wrote: > How can I have my column names stay at the top of the Excel workbook when I > scroll down? Click on the row below your headings, then Window/ Freeze Panes -- David Biddulph "Isabel" <Isabel@discussions.microsoft.com> wrote in message news:65905AF1-CBD7-4185-A43A-0BAF77BA78AB@microsoft.com... > How can I have my column n...

change day number font size of all 12 calendar pages at one time
Using the calendar template provided in Publisher 2007, I would like to change the font size of the day numbers to 24 pt in bold and I would like to do it one time instead of 12 times every time I want to make a format or font change. -- RM This link should help! http://office.microsoft.com/en-us/publisher/HP012317361033.aspx -- The Code Cage Team Regards, The Code Cage Team www.thecodecage.com ------------------------------------------------------------------------ The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2 View this thread: http://www.th...

Append 2 tables with variable names
Hi there... Here's my problem. I have 2 tables in an access 2000 database. The table names are unknown...they could have any name. Through VBA how can I append one table to the other? Hi, you can run append query: currentdb.execute "Insert into " & strTable1 & " (Field1, Field2) Select Field1, Field2 From " & strTable2, dbfailonerror Where strTable1 and strTable2 variables hold tables names -- Best regards, ___________ Alex Dybenko (MVP) http://accessblog.net http://www.PointLtd.com "ambushsinger" <ambushsinger@dis...

Selecting and then sorting a range
Hey, I had a problem that maybe someone can help me with. I'm trying to sort a range in a macro. The columns that compose the range never change, but the rows will. (i.e. columns a:o, but rows may vary from 26:400 or 26:40) I was trying to use range(selection, selection.end(xlDown)) and range(selection, selection.end(xlToRight)) The selection.end(xlDown) works fine, but I have a partially blank column in the middle of my column range. It has data for some jobs, but not all. When I do selection.end(xlTo Right), it stops at this blank. There is however, data I need sorted on the right of ...

VBA sort range with certain fontcolor
Hi all, Ik column K there will be a continuous range of which all cells have a certain fontcolor, the font color code is, say, 5. The range can be 1 cell up to 30 (or so), perhaps even zero. There are no cells in column K outside that range that also have font color code 5. While executing the code it is not (yet) known what the rownumbers of the first and last cells in that range are. I need code that will sort (descending) the following range: from row of first cell (in column K) with font color code 5 and column A to row of last cell (in column K) with font color code 5...

class private in one file?
How can I declare a class that the linker will treat private for one CPP file? Background: After hours of debugging I found the reason of (access violation 0x0005) crash. In file A.CPP I had declared a class for use only in that file something like: class CMyClass { public: double d; int a; }; In file B.CPP I had declared a class for use only in that file something like: class CMyClass { public: double d; int a; CString s; }; In file B.CPP this function crashed (access violation 0x0005): void foo() { CMyClass xx; xx.s = _T("test"); /...

How to locate differences between two sheets/ranges?
Well, here's my problem: each day here at work I receive a file with updated info on specific data. Form is always the same -specific number of columns with the same type of data, only the number of rows may change. However, every day I have to go through the data to see what has been changed and/or what has been added/dropped. Is there any way to do this easily? Kind regards Th Hi Have a look here for some ideas. I'm sure one of them will suit: http://www.cpearson.com/excel/duplicat.htm -- Andy. "Gryzor" <Gryzor@discussions.microsoft.com> wrote in message news:...

How do I change the name at the bottom of the calendar?
When I print out the calendar, the previous ofice managers' name appears at the bottom, how do I change that? "ACritchfield" wrote in message news:9CCE1BDD-8453-4025-B3F5-EE4955F1BC74@microsoft.com... > When I print out the calendar, the previous ofice managers' name > appears at > the bottom, how do I change that? Search the registry for your manager's name. It was probably the name and company he specified during the install of Windows. ...

Can you sort the sheets?
Is there any way to sort or organize or even view the sheets? - except as lifeless tabs? I have 150 sheets - i know I need a database instead of a spreadsheet - but until then..... Thnaks Look here: http://cpearson.com/excel/sortws.htm In article <nKpbb.237$ky4.126@twister.southeast.rr.com>, "mike schmidt" <mschmidt@carolina.rr.com> wrote: > Is there any way to sort or organize or even view the sheets? - except as > lifeless tabs? > > I have 150 sheets - i know I need a database instead of a spreadsheet - but > until then..... > > ...

Add Button to a sheet
I would like to add a button to a sheet (not toolbar) to do a sort on a selection of data. I have created the macro I wish to use, I would like to attached the Marco to a button. Can this be done , if so how. PR view>toolbars>forms>click on the button>cursor becomes arrow>size in area desired>click x on forms menu. Better to put on your toolbar right click toolbar>customize>forms>grab button>drag to toolbar>use from there I usually use a shape for my macro buttons. -- Don Guillett SalesAid Software donaldb@281.com "PR" <paul.raeburn@ntlw...

Syntax to match sheet by codename
I'm attempting to show/hide sheets via code, so I can see all the sheets when updating a workbook, then hide the source sheets so my users just see the output sheets. unhiding every sheet is easy; Sub ShowAllSheets() Dim sht As Worksheet For Each sht In Excel.ActiveWorkbook.Worksheets sht.Visible = xlSheetVisible Next End Sub But when trying to re-hide, I need to exclude the user sheets. I know how to do this by setting up an array of every sheet to be hidden, but that is a lot of extra code. Here is my attempt to hide everything except three user sh...

PLEASE HELP??? copy every other cell value to a range of cells
My table data 5/1 5/2 Customer A Qty1 10 10 Qty2 1 2 ------------------------------------------ Balance 9 17 Customer B Qty1 10 10 Qty2 5 2 ---------------------------------------------- Balance 5 13 I am trying to copy and paste a balance formula for each customer group. The formula is basically Qty1 (Previous cell) - Qty2(Previous cell) ...

Help! One 9175 event per minute on exchang2003 server
Hi there, Exchange2003sp1 FE + BE servers both running on w2k3 servers. BE server also act as DC. I just realised that since this sunday found that a lot of event 9175 in applicatin log, all of them are the same as follows: source: MSExchangeSA Category: MAPI Session Type: Error EventID: 9175 User: N/A Computer: EXCHANGE Description: The MAPI call 'OpenMsgStore' failed with the following error: The attempt to log on to the MS Exchange Server computer has failed. The MAPI provider failed. MS Exchange Information Store ID No: 8004011d-0512-00000000 In between those tons of 917...

Outlook 97 and HTML mails
I'm using Outlook 97 in the office and we have an Exchange server 2000. I'm receiving some mails written as HTML with a logo in it. When I receive the mail, the html-text comes as normal not formattet text, and the logo comes attached (gif-file). Why do I not get the html-text attached to the mail? Regards Jens Upgrade to a newer version as Outlook 97 is pre HTML and doesnt support it. "Jens" <i_want_no_spam@internet.com> wrote in message news:OiPWAsvnEHA.1152@TK2MSFTNGP11.phx.gbl... > I'm using Outlook 97 in the office and we have an Exchange server 2000. ...

Customer Item Maintenance One to Many Relationship
Some of our customers use one customer item number to refer to more than one of our items. For instance, their Item ABC001 may be our DDDDDEEEEE and DDDDDFFFFF and DDDDDGGGGG where "E" and "F" and "G" means a slight variation of basically the same item, lets says, for example, COLOR. So they would order an ABC001 and request the color RED or WHITE or BLUE. GP does not allow a one customer item to many manufacturer items (that I know of). Can anyone suggest a way to get around this short of asking the customer to create a bunch of new items in their system? (We ...

#name? errors on spreadsheet
I'm trying to create a spreadsheet to automatically track Word documents' date. I'm copying the date from the title page and pasting into the Excel cell using Paste Special and creating a link to the text. It works when I set it up, but when I close the spreadsheet and then reopen it the linking process shows the #NAME? error. How can I correct this? -- John Gentile, MS M(ASCP) Phone: (401) 273-7100 ext 2046 Fax: (401) 457-3069 e-mail: john.gentile@med.va.gov Laboratory Information Manager VAMC Providence Hello, Providence. I go to Wilkes Barre. :) Does the title have...

INSERT INTO in a One-toMany relationship
Hello, I am trying to do an append query in VBA that works as follows: Item(s) from a table ClientEngagement are selected in a multi-select listbox and the Engagement(s) associated with the item(s) are appended to the ClientEngagement table for the next year. (EngagementYr +1). Each Engagement has 1-10 Assignments associated with it in a table ClientAssignments which has the relationship: ClientEngagement -> one-to-many -> ClientAssignment on the fields: ClientID,EngagementID,EngagementYr -> one-to-many -> ClientID,EngagementID,EngagementYr,AssignmentID The fir...

Formula between sheets
k im have serious trouble finding a way to do this please help and i will explain as best i can i have a spreadsheet set up wit numerus pages and would like to set a formula that can be copied and pasted where the formula looks at the "previous sheet" at the same cell example onsheet 2 i wuld like to have cell e48 equal to cell e49 on sheet 1 and repeat that for every sheet 3 to 2 4 to 3 yeah i can do each one individually but i have a lot of sheets and about 40 or so cells that need this formula dont reallywant to do all that typing and on a somewhat seperate note i am a...

inserting a sheet into multiple workbooks
I have 500+ identical single-sheet workbooks in a folder and would like to insert a sheet from another workbook into all of them. Is there a way to do this in a batch w/o have to open each one and copying the sheet? Thanks. spence Hi Spence, If you are familiar with VBA you can write a module to what you are asking. The DIR function is very useful. You can load an array with all the excel workbook path+names in a given folder i.e.: MyFile = Dir(MyPath + "\*.xls") ' Load an array with all .xls file names found in the given path aFileArray(1) = MyPath + "\" + MyFil...

Adjusting sheet size
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel If I put the cursor on the lower right hand corner of the sheet, I can no longer adjust the size of the sheet. How do I fix this? Thanks It's most likely a Protected workbook... Have a look in Tools> Protection to see if it lists an 'Unprotect Workbook' command. You may need a password to remove the protection. HTH |:>) Bob Jones [MVP] Office:Mac On 6/4/10 9:52 AM, in article 59bb95c0.-1@webcrossing.JaKIaxP2ac0, "investduke@officeformac.com" <investduke@officef...

Named range row/column reference #2
thanks. understood, and that does work well. I would still like to be able to reference within named ranges usin offsets from the beginning of the named range. Once I have the offset they would serve as references into other named ranges of a simila dimension. *Is there a way to get vStudentNbr and vTestWeek as relative reference to the beginning of their respective named ranges? * (yes I am a VBA newbie!! and I am not yet writing the most efficien code LOL :) -- Urklnm ----------------------------------------------------------------------- Urklnme's Profile: http://www.e...

Why do the colors or shading only show up in print preview in Exce
I have attempted to follow directions for inserting color and shading in cells in Excel, but the color only shows in print preview, not in the window I am working in.........WHY is this happening? Hi! See if this helps: http://support.microsoft.com/?id=320531 Biff "voiceqween" <voiceqween@discussions.microsoft.com> wrote in message news:71986630-E420-4036-9F00-3C5A087B6884@microsoft.com... >I have attempted to follow directions for inserting color and shading in > cells in Excel, but the color only shows in print preview, not in the > window > I am working ...

Referencing a Sheet and Cell Location in Change Event of Combo Box
I have a combo box in Sheet1 of my workbook I would like after any ite is changed in it to go to Cell A4 of my Rates Worksheet in the sam workbook. Here is the code Private Sub ComboBox1_Change() Sheets("Rates").Select Range("A4").Select End Sub It is breaking on this line here Range("A4").Select What do I need to change so that it can go to A4 of my Rates worksheet -- Message posted from http://www.ExcelForum.com Use this Sheets("Rates").Activate Sheets("Rates").Range("A4").Select Or Application.Goto Reference:=Worksheets...

Make one row a constant display?
Hi all, I've been searching Google, but I haven't found anything, yet. I wanted to make a row in my list constant, so I can correlate the data with its type easily. The data type is specified in the first row of the list. I thought Pivot Tables would do the job, but I guess it's the wrong function. Any tips how I can do this? My list looks like: Name | Grade | Level ------------------------------ Joe1,A,12 Jim2,B,13 ........ I want it so that when I scroll down the list Name, Grade, and Level stay put. Thanks for the tip! MRW, select A2, then window, freeze panes -- Paul B...