Microsoft Office Tutorials and References
In Depth Information
Referencing names
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 work-
sheet.
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 this name:
=Total_Sales
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:
=North_Sales
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 see workbook-level names and
worksheet-level names only as defined on Sheet1. To refer to South_Sales on Sheet1, prefix the name with the
worksheet name and an exclamation point:
=Sheet2!South_Sales
If your worksheet name contains a space, enclose the worksheet name in single
quotes when referring to a name defined on that sheet:
='My Sheet'!My_Name
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 Formu-
las ⇒ Defined Names ⇒ 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.
For example, the following formula uses a range named MonthlySales, a workbook-level name defined in a
workbook named Annual Budget.xlsx (which is assumed to be open):
Search JabSto ::




Custom Search