The Context Of Names
When you define a range name using the methods described so far, you will always get a defined name, which is visible throughout an entire workbook.
In general this kind of name is referred to as a global name. The workbook is their parent object. Names can also be defined local to a worksheet. By prefixing the worksheet's name with the name, you create a local name (See fig. 9).
Fig. 9: defining a local name
If the name of the worksheet contains spaces or other special characters, you must surround its name with single quotes:
The Define Names dialog only shows global names and names that are local to the active worksheet. Local names can be discerned from global ones because their sheet name is shown in the Define Name dialog (see Fig. 10).
Fig. 10: Local name in Define Name dialog
When a name has both a global and a local version, confusion may arise.
When the worksheet that the local name belongs to is selected, only the local name is shown and can be modified. To access the global name, one must select a worksheet that has not got a local name with the same name as the global one.
On a worksheet with a local name it is not possible to use the global name, Excel will always use the local name. On other worksheets, Excel will default to the global name, unless you precede it with the worksheet name:
When one creates a copy of a worksheet to which one or more global names refer, Excel will automatically create copies of those names local to the copied worksheet. Fig. 11 shows the result of copying Sheet1:
Fig. 11: Define Name dialog after copying a worksheet to which global names refer
Note, that the (identically named) global names are not shown in the dialog at all, to access those, you have to select another worksheet.