How to create local names (vs. global names)

[Thomas Staudte]

Hello NG,

that might be a simple problem, but I have so far not been able to find a
solution myself.

Whenever I define a name in a worksheet it becomes a global name. When I
define the same name in another worksheet the first one is being replaced.
How can I make names local to an individual worksheet? I want to have the
same name defined on every worksheet so that I can copy formulas easily between
worksheets.

So far I have only managed to create local names by duplicating entire worksheets,
where the names were defined beforehand, which converted the global into
local names. While this is fine for an initial set-up of a workbook, there
must surely be also a way to define local names later, too, isn't it?

Thank you very much for any hints.

Best regards,

Thomas Staudte

Staudte EDV Beratung
www.staudte-edv.de
Germany

 

[Pete_UK]

When you create the name (with Insert | Name | Create) put the sheet
name in front of the name you wish to use, eg "Sheet1!name" and when
you refer to the names always use the sheet name to ensure you refer to
the correct named range.

Hope this helps.

Pete

 

[Bob Phillips]

Ensure that the worksheet that you want the name to apply to is active and
then just prefix the name by the sheet name, such as

Sheet1!myName

The refersto value is set as normal.

When referring to that name from some other worksheet, again, prefix by the
sheet name.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Thomas Staudte]

Pete and Bob,

thank you _very_ much for this _extremely_ fast and helpful response!

Best regards,

Thomas Staudt

 

[Dave Peterson]

You may want to 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 working with names much, much easier.

 

[Pete_UK]

Thanks for the feedback.

Pete