XL5: Using Global and Local References in Formulas

ID: Q107197

The information in this article applies to:

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" (without the quotation marks) and in the Refers To box, type "Sheet1!$A$1:$A$10" (without the quotation marks). 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.

MORE INFORMATION

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. 

   To Refer to this name     On this sheet     Use this formula
   ------------------------------------------------------------

   Sheet1!Sales              Sheet1            =Sales
   Sheet1!Sales              Sheet2            =Sheet1!Sales
   Sheet1!Sales              Sheet3            =Sheet1!Sales
   Sheet2!Sales              Sheet1            =Sheet2!Sales
   Sheet2!Sales              Sheet2            =Sales
   Sheet2!Sales              Sheet3            =Sheet2!Sales
   Sales                     Sheet1            =Sheet3!Sales (see
                                                NOTE below)
   Sales                     Sheet2            =Products!Sales
   Sales                     Sheet3            =Sales
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. 

                             On a worksheet
   To Refer to this name     in this workbook      Use this formula
   -----------------------------------------------------------------

   Sheet1!Sales              Workbook2             =[Products]Sheet1!Sales
   Sheet2!Sales              Workbook2             =[Products]Sheet2!Sales
   Sales                     Workbook2             =Products!Sales
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.

REFERENCES

"User's Guide," version 5.0, pages 144-150

For more information about Naming a Cell, Range or Formula, choose the Search button in Help and type: 

    naming
Additional reference words: 5.00 
Keywords          : kbprg kbprb 
Version           : 5.00
Platform          : WINDOWS

Last Reviewed: September 3, 1997