Microsoft Office Tutorials and References
In Depth Information
Tip 31: Creating Worksheet-Level Names
Creating Worksheet-Level Names
Normally, when you name a cell or range, you can use that name in all worksheets in the workbook.
For example, if you create a name, say RegionTotal , that refers to the cell M32 on Sheet1, you can use
this name in any formula in any worksheet. This name is a workbook-level name (or a global name).
By default, all cell and range names are workbook-level names.
Suppose that you have several worksheets in a workbook (one for each region) and you want to use
the same name (such as RegionTotal ) on each sheet. In this case, you need to create worksheet-level
names (sometimes referred to as local names).
To define the worksheet-level name RegionTotal , activate the worksheet in which you want to define
the name and choose Formulas➜Defined Names➜Define Name. The New Name dialog box then
appears. In the Names field, enter the name in the Name field and use the Scope drop-down list to
select the sheet in which the name is valid. Figure 31-1 shows a worksheet-level name being created.
Figure 31-1: Creating a worksheet-level name.
You can also create a worksheet-level name by using the Name box (located to the left of the
Formula bar). Select the cell or range you want named, click in the Name box, and type the name,
preceded by the sheet name and an exclamation point. Press Enter to create the name. Here’s an
example of a worksheet-level name: