Microsoft KB Archive/111905

= Microsoft Knowledge Base =

XL5: Can't Define Global Name If Same Local Name Exists
Last reviewed: September 12, 1996

Article ID: Q111905

The information in this article applies to:


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

SYMPTOMS
In Microsoft Excel, if you define a global name, when a local name with the same name exists on the active worksheet, the value that the local name refers to is the value you defined for the global name and the global name is not defined on the workbook.

Additionally, in Microsoft Excel version 5.0 for Windows ONLY, when you add a global name using the Add button in the Define Name box, when a local name with the same name exists on the active worksheet, the global name appears in the Names In Workbook list, even though the global name is not created.

WORKAROUND
To create a global name and a local name with the same name in a workbook, do either of the following:

 Create the global name first, and then create the local name. -or- Create the global name on a worksheet other than the worksheet on which the local name is defined.

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

Note that you still cannot define a global name, when a local name with the same name exists on the active worksheet. However, when you do this in Microsoft Excel version 5.0c for Windows, or Microsoft Excel version 5.0 for the Macintosh, the global name is no longer added to the Names In Workbook list.

MORE INFORMATION
If you create the local name "Sheet1!Sales" on Sheet1 that refers to the value 5, and then create the global name "Sales" that refers to the value 10 while Sheet1 is active, the local name "Sheet1!Sales" now refers to the value 10, and the name "Sales" cannot be referenced on any other sheet in the workbook.

For additional information, please see the following article(s) in the Microsoft Knowledge Base:

ARTICLE-ID: Q107197 TITLE    : XL5: Using Global and Local References in Formulas

ARTICLE-ID: Q107077 TITLE    : XL5: Can't Reference Global Name on First Sheet