Context

JKP Application Development Services.

Microsoft Office Application Development

Context

Up • Introduction • Define • Use • Absolute/Relative • Context • Special Names • Formula In Name • Dynamic Names • XLM In Names • Arguments • Bugs Name Obj. • Conclusion

• •

Search

Zoeken

Go to	Ga Naar
Home Up

Donate	Donaties
Did you find something helpful on my site? Consider a donation! Heeft u iets gevonden waar u wat aan had? Overweeg dan een donatie!

Range Names In Excel

Introduction 1 2 3 4 5 6 7 8 9 10 Conclusion

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.

<< Previous Next >>

Subscribe in a reader