Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Defined Names > Context
Deze pagina in het Nederlands

Range Names In Excel

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:

‘Sheet1 (2)’!Localname

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:

=Sheet3!LocalName

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.