XL: Links Return #REF Error Value

ID: Q100717

The information in this article applies to:

SYMPTOMS

In Microsoft Excel, when a cell being referenced within a link is moved to another worksheet, Microsoft Excel does not see that the reference has been moved. The link behaves as if the cell were deleted and returns a #REF! error value. But, if the cell is moved within the same worksheet, the reference will be updated as expected.

This behavior will also occur if you move cells on top of the cells being referenced by dragging the new cells over the referenced cells or by using the Cut and Paste commands.

This situation also applies to workbooks in Microsoft Excel, version 4.0.

This feature is by design of Microsoft Excel.


WORKAROUND

To avoid receiving a #REF! error value when dragging or pasting cells on top of externally referenced cells, use the INDIRECT() function or the OFFSET() function to reference the cell indirectly. For example, to reference cell A1 in Sheet1, use one of the following formulas in cell A2 of Sheet1.

Microsoft Excel versions 5.0, 7.0


   =INDIRECT("Sheet1!A1") 



   -or- 



   =OFFSET(Sheet1!A2,-1,0)


Microsoft Excel versions 2.x, 3.0, 4.0


   =INDIRECT("Worksheet1!A1") 



   -or- 



   =OFFSET(worksheet1.xls!A2,-1,0)


Note, neither of these two functions, if used to reference cell A1, will return the #REF! error, but they also will not update the cell reference within the formula. So, in this example, if cell A1 is cut and pasted into another worksheet, cell A2 will still reference cell A1 and will show a value of 0.


REFERENCES

"User's Guide 1," version 4.0, page 192
"User's Guide," version 3.0, page 160

Additional query words: 2.00 2.01 2.10 2.10c 2.10d 4.00 4.00a 


Keywords          : 
Version           : WINDOWS: 2.0,3.0,4.0,5.0,5.0c,7.0; MACINTOSH: 2.2,3.0,4.0,5.0,5.0a
Platform          : MACINTOSH WINDOWS 
Issue type        : kbprb

Last Reviewed: April 5, 1999