Microsoft KB Archive/107197

{|
 * width="100%"|

XL5: Using Global and Local References in Formulas

 * }

-

The information in this article applies to:


 * Microsoft Excel for the Macintosh, version 5.0
 * Microsoft Excel for Windows, version 5.0

-

SUMMARY
In Microsoft Excel, you can define names on a worksheet as either global or local names.

Global Names
Global names are available to the entire workbook. For example, if you define cells A1:A10 on Sheet1 as Sales, you can sum the sales on Sheet2 with the formula =SUM(Sales). When you use a global name, you do not need to indicate a sheet name because a global name is available to any sheet in the workbook.

Local Names
A local name is available only on the sheet where it is defined. You create a local name by including the sheet name with an exclamation point (!) before the name in the Define Name dialog box. For example, in the Name box, type Sheet1!Sales and in the Refers To box, type Sheet1!$A$1:$A$10. To use this name on a another sheet in the workbook, create an external reference, for example, type:

  =SUM(Sheet1!Sales) Local names take precedence over global names. To refer to a global name that duplicates a local name on the active sheet, use an external reference by specifying the workbook name.

The following discusses the ways to refer to global and local names, both on the current workbook and on another workbook.

Referring to Global and Local Names Located on the Current Workbook
The following table uses as an example a workbook called "Products" which contains the worksheets "Sheet1," "Sheet2," and "Sheet3." Sales is a global name on Products and is also a local name defined on both Sheet1 and Sheet2 but not on Sheet3. Sheet1!Sales is the name of the local Sales on Sheet1. Sheet2!Sales is the name of the local Sales on Sheet2. Sales is the name of the global Sales on Products.

NOTE: To refer to the global name Sales from Sheet1 which also contains the local name Sales, you must use the name of a worksheet in the workbook that does not contain the local name Sales. In this case, Sheet3 must be used in the reference to the global name Sales. For more information on this exception involving the first worksheet in a workbook, query on the following words in the Microsoft Knowledge Base:

"reference and global and first"

Search Order of Global and Local Names on the Current Workbook
If you enter the formula =Sales on a worksheet in Products, the following search order is used:

Local Sales on the current worksheet in Products

Global Sales on Products If you enter the formula =Sheet1!Sales or the formula =[Products]Sheet1!Sales on a worksheet in Products, the following search order is used:

Local Sales on Sheet1

Global Sales on Products If you enter the formula =Products!Sales on a worksheet in Products, the formula will look for the following defined name:

"Global Sales on the Products workbook (see NOTE above)"

Referring to Global and Local Names Located on Another Workbook
The following table uses Workbook2, as well as the Products workbook defined above.

To refer to a global or local name on another workbook, you must include the workbook name.

Search Order of Global and Local Names on Another Workbook
If you enter the formula =[Products]Sheet1!Sales on a worksheet in Workbook2, the following search order is used:

Local Sales on Sheet1

Global Sales on Products If you enter the formula =Products!Sales on a worksheet in Workbook2, the formula will look for the following defined name:

"Global Sales on the Products workbook" If the name Sales is found in the first step of the search, that value of Sales is returned, and the search is complete. If the name Sales is not found after the final step in the search, the #NAME! error will be returned.