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

Last reviewed: February 2, 1998
Article ID: Q178425
The information in this article applies to:
  • Microsoft Excel 98 Macintosh Edition

SYMPTOMS

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

   Microsoft Excel cannot find '<Name>' on '<Workbook>'. 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.

where <Name> represents the defined name and <Workbook> represents the name of the workbook (not the active workbook) that contains the defined name.

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

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

CAUSE

This problem occurs if the following conditions are true:

  • The formula is linked to a defined name that is contained in another workbook.

    -and-

  • 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.

    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 then 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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.


Additional query words: XL98
Keywords : xlformula
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbbug
Solution Type : kbworkaround


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: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.