Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

XL2000: #REF! Error Recalculating Worksheet with Defined Name


View products that this article applies to.

This article was previously published under Q221058

↑ Back to the top


Symptoms

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

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


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.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
  11. Type the following formula in cell A1 of Sheet2:

    =two
  12. On the File menu, click Save As. Type Client.xls in the File name box, and then click Save.
  13. Close Client.xls and Source.xls.
  14. Open Client.xls. Click Yes when you are asked to update links.
Cell A1 on Sheet2 displays the #REF! error code.

↑ Back to the top


Keywords: KB221058, kbpending, kbbug

↑ Back to the top

Article Info
Article ID : 221058
Revision : 3
Created on : 11/5/2003
Published on : 11/5/2003
Exists online : False
Views : 261