Microsoft KB Archive/221058

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

Article ID: 221058

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q221058





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.



RESOLUTION
To resolve this problem, obtain Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a).

To obtain SR-1/SR-1a, click the article number below to view the article in the Microsoft Knowledge Base:

245025 OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a)

To temporarily work around this 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. This problem was corrected in Microsoft Office 2000 SR-1/SR-1a.



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) On the Insert menu, point to Name and then click Define.
 * 7) In the Names in workbook box, type One . Type =[source.xls]sheet1!a1 in the Refers to box, and then click Add.
 * 8) In the Names in workbook box, type Two . 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: XL2000 recalc link

Keywords: kbbug kbpending KB221058

-

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

© Microsoft Corporation. All rights reserved.