create local defined name range

[Gates Is Antichrist]

How can I add a duplicate named range, local to a worksheet? When I insert/name/define, the "master one" (vs. local) disappears from another sheet.

Sheet1 has range Database. I call it "master" - it's the one that F5 sends you to.
Sheet2 has a local range Database.
Sheet3 has a local range Database.
I want to put a local range Database on Sheet4. But insert/name there wipes out the defined name on Sheet1.

Somehow I managed the trick on sheet2 and 3, probably by importing. What's the more direct way to do it?

Hey, #500 as I write this.

 

[Zack Barresse]

Hey GIA,

Define your name as ...


=!A1


Or wherever you want it to go. This should make it whatever sheet you're on. Although, imho, this is a very unstable way to do things, as named ranges go cross-sheets; meaning that one named range will generally and usually work for the entire book, hence the reason you usually can't duplicate named range names.


And congrats on the 500!!!

 

[Gates Is Antichrist]

Sorry, I'm not quite with you. I went to sheet4 and went insert/name/define and typed Database and removed the substring sheet4 from the reference and hit OK. It didn't seem to create anything.

BTW I'm not looking for a range that means the same dimensions on all sheets, which it sounds like you're desctibing.

I agree that the inherent ambiguity introduced by duplicative names is generally nasty (and especially "floating reference" names - AARRRRRRGGGH). However this would seem to be a squeaky clean exception, for having a filter specific to each of several pages.

 

[Aladin Akyurek]

Sorry, I'm not quite with you. I went to sheet4 and went insert/name/define and typed Database and removed the substring sheet4 from the reference and hit OK. It didn't seem to create anything.

BTW I'm not looking for a range that means the same dimensions on all sheets, which it sounds like you're desctibing.

I agree that the inherent ambiguity introduced by duplicative names is generally nasty (and especially "floating reference" names - AARRRRRRGGGH). However this would seem to be a squeaky clean exception, for having a filter specific to each of several pages.

Include the sheet name in the name itself like in:

Sheet4!Database

 

[Gates Is Antichrist]

Thank you, but doing that is creating a new "master" if you follow me from above. It's not creating a local one.

What you suggested is already the default content of the "refers to field."

Yes, the local ones "look" like that in the "refers to field" but I need to force the locality - ya' know?

 

[Aladin Akyurek]

Thank you, but doing that is creating a new "master" if you follow me from above. It's not creating a local one.

What you suggested is already the default content of the "refers to field."

Yes, the local ones "look" like that in the "refers to field" but I need to force the locality - ya' know?

Master!Database

Refers to, say:

=Master!$B$4:$F$8

Sheet1!Database

refers to, say:

=Sheet1!$A$2:$D$40

so on.

 

[Gates Is Antichrist]

Oh, I'm sorry - you said to put the sheet name in the range name itself. I thought you meant the Refers to box. You were clear, I was dense. My dumb - thanks.

 

[FastExcel]

My advice is :
- do not create global names that are the same as local (duplicate Global Local): Excel does unexpected things.
- do not create names that refer to =!ref : there are Excel bugs that can give incorrect calculations

If you use Defined Names download Name Manager (by myself and Jan Karel Pieterse) from
http://www.DecisionModels.com/downloads.htm

 

[Gates Is Antichrist]

It's an honor to hear from you, Charles. If YOU say something is nasty - I'll try to take heed. And I did d/l Name Manager. Thank you. XL's built in name interface is not stellar so I'm hungry for it.

Do you remember the symptoms of Excel's misbehavior off the top of your head, e.g. with the "!REF"?

 

[leibale]

Hey GIA,
Define your name as ...
=!A1
Or wherever you want it to go. This should make it whatever sheet you're on. Although, imho, this is a very unstable way to do things, as named ranges go cross-sheets; meaning that one named range will generally and usually work for the entire book, hence the reason you usually can't duplicate named range names.
===
1. Very interesting and useful idea!
2. as I understand, Global name is a unique name, and local name is a name that appear is several sheets. is it?