Microsoft Office Tutorials and References
In Depth Information
A Name’s Scope
You can refer to a workbook-level name just by using its name from any sheet in the workbook.
For worksheet-level names, you must precede the name with the name of the worksheet unless
you’re using it on its own worksheet.
For example, assume that you have a workbook with two sheets, Sheet1 and Sheet2. In this
workbook, you have Total_Sales (a workbook-level name), North_Sales (a worksheet-level name on
Sheet1), and South_Sales (a worksheet-level name on Sheet2). On Sheet1 or Sheet2, you can refer
to Total_Sales by simply using the name:
If you’re on Sheet1 and you want to refer to North_Sales, you can use a similar formula because
North_Sales is defined on Sheet1:
However, if you want to refer to South_Sales on Sheet1, you’ll need to do a little more work.
Sheet1 can’t “see” the name South_Sales because it’s defined on another sheet. Sheet1 can only
see workbook-level names and worksheet-level names defined on Sheet1. To refer to South_Sales
on Sheet1, prefix the name with the worksheet name and an exclamation point:
If your worksheet name contains a space, enclose the worksheet name in single quotes
when referring to a name defined on that sheet:
Generally, it’s a good practice to scope your names as narrowly as possible. If you want to use a
name on only one worksheet, set that name’s scope at the worksheet level. For names that you
want to use throughout your workbook, a workbook-level scope is more appropriate.
Only the worksheet-level names on the current sheet appear in the Name box. Similarly,
only worksheet-level names on the current sheet appear in the list under Formulas➜
Use in Formulas.
Referencing names from another workbook
Chapter 2 describes how to use links to reference cells or ranges in other workbooks. The same
rules apply when using names defined in another workbook.