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
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
=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 Last reviewed: March 27, 1997 |