Microsoft KB Archive/214376

= XL2000: Error Using Defined Name That Refers to a Constant Value =

Article ID: 214376

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214376





SYMPTOMS
When you enter a formula in a cell in Microsoft Excel, or when you open a workbook in Microsoft Excel, you may receive the following error message

Microsoft Excel cannot find '' on ''.

where represents the defined name and  represents the name of your workbook. After you receive the error message, any formulas in the workbook that refer to the defined name return the #REF! error value (invalid cell reference).



CAUSE
This problem occurs if the following conditions are true:
 * The workbook that contains the defined name is not open.

-and-
 * The defined name refers to a constant value instead of a cell reference or another defined name, for example: =37

="Hello"



WORKAROUND
To work around this problem, use either of the following methods.

Method 1
Open the workbook that contains the defined name.

When the workbook that contains the defined name is open, formulas in other workbooks that refer to the defined name return the correct value.

Method 2
Instead of storing the constant value in the defined name, store the value in a worksheet cell in the workbook, and then change the defined name so that it refers to the worksheet cell. For example, if the defined name refers to the value "=37", follow these steps:
 * 1) Type the constant value (for example, 37 ) in a cell (for example, B5) in a worksheet in the workbook.
 * 2) On the Insert menu, point to Name and click Define.
 * 3) In the Names in workbook edit box, type the name you want to define.
 * 4) In the Refers to edit box, type =Sheet1!$B$5 . Or, select the cell.
 * 5) Click OK.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
Keywords: kberrmsg kbbug kbpending KB214376

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.