Microsoft KB Archive/107197

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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.



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.


To Refer to this name On a worksheet 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 query words: 5.00

Keywords : kbprg kbprb
Version : MACINTOSH:5.0; WINDOWS:5.0
Platform : MACINTOSH WINDOWS
Issue type :
Technology :


Last Reviewed: October 29, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.