XL: Making All Identical Names in a Workbook Local

ID: Q104501

The information in this article applies to:

SUMMARY

In Microsoft Excel, a defined name can be either global or local. When you move or copy a sheet containing one or more names that duplicate global names in the destination workbook, the names on the newly added sheet are converted to local names. Names that are not duplicates, remain global names. This combination of identical names that are global on one sheet and local on others may be confusing and possibly return unexpected results if it isn't clear which names are global, which are local, and how to refer to the correct one.

The following information discusses global and local names and how you can refer to the name you want to use. It also provides an easy method for making all duplicate names in a workbook local.


MORE INFORMATION

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 the reference is not external; 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. When you define a local name, you include 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 separate sheet, you create an external reference (also the local name); for example, =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.

Making Identical Names Local

To make all of the identical defined names local, save the workbook as a Microsoft Excel 4.0 workbook:

  1. From the File menu, choose Save As.


  2. Under Save File As Type, select Microsoft Excel 4.0 Workbook.


  3. Choose OK.


  4. From the File menu, choose Close.


When you reopen the workbook, each identical name is redefined as a local name. You can then save the workbook in Microsoft Excel 5.0 format by selecting Microsoft Excel Workbook under Save File As Type in the Save As dialog box.

Additional query words: 4.00 4.00a 3.00 collision conflict 


Keywords          : 
Version           : WINDOWS:97,7.0,5.0; MACINTOSH:5.0,5.0a
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 7, 1999