Microsoft KB Archive/222844

= XL97: #REF! Error Recalculating Worksheet with Defined Name =

Article ID: 222844

Article Last Modified on 10/6/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q222844





SYMPTOMS
When your worksheet is recalculated, some 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.



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.xls in the File name 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.xls]sheet1!a1 in the Refers to box, and then click Add.
 * 8) Type Two in the Names in workbook box. Type =[source.xls]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
 * 1) Type the following formula in cell A1 of Sheet2:

=two
 * 1) On the File menu, click Save As. Type Client.xls in the File name box, and then click Save.
 * 2) Close Client.xls and Source.xls.
 * 3) Open Client.xls. Click Yes when you are asked to update links.

Cell A1 on Sheet2 displays the #REF! error code.

Additional query words: XL97 recalc link

Keywords: kbbug kbpending KB222844

-

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

© Microsoft Corporation. All rights reserved.