Microsoft Knowledge Base

XL: Can't Reference Global Name on First Sheet
Last reviewed: March 27, 1997
Article ID: Q107077
5.00 7.00 | 5.00 
WINDOWS   | MACINTOSH
kbusage kbtlc

The information in this article applies to:

  • Microsoft Excel for Windows, version 5.0
  • Microsoft Excel for the Macintosh, version 5.0
  • Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

On the first worksheet in a workbook in Microsoft Excel, you will not be able to refer to a global name when there is a local name on the first worksheet that is the same as the global name.

WORKAROUND

To work around this situation, do one of the following:

  • Do not use duplicate global and local names in a workbook.

    -or-

  • When the first worksheet contains a duplicate local name, use the following formula to refer to a global name on that first worksheet 

           =Sheet!GlobalName

    where "Sheet" is the name of any worksheet in the current workbook that does not contain a local name that is the same as "GlobalName."

    -or-

  • When the first worksheet contains a duplicate local name and every other worksheet in the workbook also contains a duplicate local name, do the following to refer to a global name on the first worksheet in the workbook:

    1. Enter the following global reference in a worksheet other than 

          the first worksheet in the workbook:


             =Book!GlobalName

   2. Copy this reference and paste it in the first worksheet. The
      global name is referenced correctly.

MORE INFORMATION

Normally, to refer to a global name on a workbook, only the global name or the workbook name and the global name are necessary. For example, to refer to the global name "test" on any sheet in the workbook "book1," use the following formula: 

   book1!test
However, to refer to a global name on the first worksheet when the first worksheet contains a duplicate local name, you must use the name of another worksheet in the workbook along with the global name. For example, to refer to the global name "test" on sheet1, when sheet1 contains the local name "sheet1!test," use the following formula 

   sheet2!test
where "sheet2" is the name of any worksheet in the current workbook that does not contain "test" as a local name. If you use "book1" or "sheet1" in place of "sheet2" in the above example, the local name "test" is referenced instead of the global name.

Steps to Reproduce Behavior

  1. In the first worksheet of a new workbook, enter the following data: 

          A1: global
      A2: local

  2. Select cell A1, and from the Insert menu, choose Name and choose Define.

  3. In the Names In Workbook box, type "test" (without the quotation marks) and choose OK.

  4. Select cell A2, and from the Insert menu, choose Name, and choose Define.

  5. In the Names In Workbook box, type "sheet1!test" (without the quotation marks) where "sheet1" is the name of the active worksheet, and choose OK.

  6. In cell B1, enter the following formula 

          =book1!test

    where "book1" is the name of the current workbook.

The value of the cell is "local" even though the formula entered refers to the global name. Select cell B1 and note that the reference changes to the following: 

   =sheet1!test
This behavior only occurs in the first worksheet in the workbook.

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

   ARTICLE-ID: XL5: Can't Define Global Name if Same Name Exists
   TITLE     : Q111905

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

KBCategory: kbusage kbtlc
KBSubcategory:

Additional reference words: 5.00 7.00
Keywords : kbtlc kbusage
Version : 5.00 7.00 | 5.00
Platform : MACINTOSH WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 27, 1997
©1997 Microsoft Corporation. All rights reserved. Legal Notices.