XL98: #REF! Error Recalculating Worksheet with Defined Name (222845)


The information in this article applies to:

  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q222845

SYMPTOMS

When your worksheet is recalculated, come cells may display the #REF! error code.

CAUSE

This problem may occur if the following conditions are true:
  • The cells that contain the #REF! error code refer to a defined name.

    -and-

  • The defined name refers to an external file.

    -and-

  • The defined name contains a relative cell reference to a cell in the external file.

WORKAROUND

To work around the problem, type the reference to the external file in the cell, rather than using a defined name to refer to the external file.

STATUS

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

MORE INFORMATION

Example of the Problem

The following steps illustrate the problem:
  1. Create a new workbook that contains at least two worksheets.
  2. Type the value 1 in cell A1 of Sheet1.
  3. Type the value 2 in cell A1 of Sheet2.
  4. On the File menu, click Save As. Type Source in the Save as box, and then click Save.
  5. Create a new workbook that contains at least two worksheets.
  6. Point to Name on the Insert menu and then click Define.
  7. Type One in the Names in workbook box. Type =[source]sheet1!a1 in the Refers to box, and then click Add.
  8. Type Two in the Names in workbook box. Type =[source]sheet2!a1 in the Refers to box, and then click Add.
  9. Click Close to dismiss the Define Name dialog box.
  10. Type the following formula in cell A1 of Sheet1:

    =one
  11. Type the following formula in cell A1 of Sheet2:

    =two
  12. On the File menu, click Save As. Type Client in the Save as box, and then click Save.
  13. Close the Client and Source files.
  14. Open the Client file. Click Yes when you are asked to update links.
Cell A1 on Sheet2 displays the #REF! error code.
Modification Type:MajorLast Reviewed:9/11/2002
Keywords:kbbug KB222845
©2002 Microsoft Corporation. All rights reserved.