Scope of Names of Cell Reference

So as I understand it, a *name* in any worksheet of a particular
workbook will have workbook-wide scope, whether you want it to or not.

In other words, if you have three worksheets, WS1, WS3, and WS3, all
in WB1, you had better make sure that you use different names for cell
ranges in each of the worksheets even though what you chose to call
the row and column headings in each of them be the same - using the
name of of the worksheet in a cell specification does not help.

If this is true, it is rather annoying.

-Chaud Lapin-

That's not correct. You can have worksheet level names as well as
workbook level names.

One way to create a worksheet level name is to prefix the name with the
sheet name when you enter it in the Name Box on the Formula Bar. So
instead of entering

myname

enter

WS1!myname

for sheet WS1.

myname will still refer to the workbook level name in other sheets, but
within WS1, it will refer to the worksheet name.

In article ,
(Le Chaud Lapin) wrote:

So as I understand it, a *name* in any worksheet of a particular
workbook will have workbook-wide scope, whether you want it to or not.

In other words, if you have three worksheets, WS1, WS3, and WS3, all
in WB1, you had better make sure that you use different names for cell
ranges in each of the worksheets even though what you chose to call
the row and column headings in each of them be the same - using the
name of of the worksheet in a cell specification does not help.

If this is true, it is rather annoying.

JE McGimpsey wrote in message ...
That's not correct. You can have worksheet level names as well as
workbook level names.

One way to create a worksheet level name is to prefix the name with the
sheet name when you enter it in the Name Box on the Formula Bar. So
instead of entering

myname

enter

WS1!myname

for sheet WS1.

myname will still refer to the workbook level name in other sheets, but
within WS1, it will refer to the worksheet name.

Looks to me like scoping of names at worksheet vs. workbook level only
works for names that are explicitly defined, not labels that Excel
uses for inference. Seems like it gets confused about labels, whether
there are prefix with worsheet scoping operator (!) or not.

That the case?

-Chaud Lapin-

No. XL isn't confused at all, but the interface may seem inconsistent...

If you use a designator for a worksheet level name that is not also a
workbook level name, you can create the worksheet level name in the
Name box using

Sheet1!myrng

Once a workbook level name is defined, you can only define a worksheet
name with the same designator explicitly in Insert/Name/Define dialog
(or using VBA).

I suspect that this is a "feature" - since the Name box can be used to
both define a name and select a range defined by the name, it may be a
design decision to disallow potentially inadvertent overwriting of
existing workbook-level names. Alternatively, it may have been a
screw-up. However, it's quite consistent.

In article ,
(Le Chaud Lapin) wrote:

Looks to me like scoping of names at worksheet vs. workbook level only
works for names that are explicitly defined, not labels that Excel
uses for inference. Seems like it gets confused about labels, whether
there are prefix with worsheet scoping operator (!) or not.

That the case?

Le Chaud Lapin wrote:
Seems like it gets confused about labels, whether
there are prefix with worsheet scoping operator (!) or not.

That the case?


That's how it looks to me.
If you can, avoid the use of labels.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup