Microsoft Knowledge Base

• 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