Microsoft KB Archive/107077
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
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
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.
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
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."
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.
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:
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
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
In the first worksheet of a new workbook, enter the following data:
A1: global A2: local
- Select cell A1, and from the Insert menu, choose Name and choose Define.
- In the Names In Workbook box, type "test" (without the quotation marks) and choose OK.
- Select cell A2, and from the Insert menu, choose Name, and choose Define.
- 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.
In cell B1, enter the following formula
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:
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
"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:
KBCategory: kbusage kbtlc
Last reviewed: March 27, 1997