XL2000: #REF! Error Recalculating Worksheet with Defined Name

• Microsoft Excel 2000

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 this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Example of 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.

Additional query words: XL2000 recalc link

Keywords :
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug