XL: Error Using Defined Name That Refers to a Constant Value

ID: Q171951

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, versions 7.0, 7.0a

SYMPTOMS

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

   Microsoft Excel cannot find '<Name>' on '<Workbook name.xls>'.
   There are two possible reasons:

    - The name you specified may not be defined.

    - The name you specified is defined as something other than a
      rectangular cell reference. Check the name and try again.

Note that <name> represents the defined name and <Workbook name.xls> represents the name of your workbook.

You receive this error message even through the defined name is present in the workbook.

After you receive the error message, any formulas in the workbook that refer to the defined name return the #REF! error value.

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:

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.
```
    -or-
```

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" (without the quotation marks), 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 box, type the name you want to define.
       In the Refers To box, type "=Sheet1!$B$5" (without the quotation
       marks). Or, select the cell. When you are done, click OK.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

Additional query words: XL97 XL7 7.0

Keywords          : kberrmsg xlformula 
Version           : WINDOWS:7.0,7.0a,97
Platform          : WINDOWS
Issue type        : kbbug

Last Reviewed: January 7, 1999