XL: Links Return #REF Error Value

 Last reviewed: February 2, 1998
Article ID: Q100717
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 2.2 , 3.0 ,4.0, 5.0, 5.0a
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, version 7.0

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: 5.00 2.00 2.01 2.10 2.10c 2.10d 4.00 4.00a 3.00
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
Solution Type : kbworkaround

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.