Microsoft KB Archive/182191

= XL2000: Link Formula Returns #REF! Error Value =

Article ID: 182191

Article Last Modified on 10/6/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q182191



SYMPTOMS
In Microsoft Excel, when you move (cut and paste) or delete cells on a worksheet, formulas may return the #REF! error value.



CAUSE
This problem occurs when you delete or move a cell that is referenced in a link formula.



WORKAROUND
To avoid receiving a #REF! error value when you drag or paste cells on top of referenced cells, or delete 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:

=INDIRECT("Worksheet1!A1")

-or-

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

NOTE: Neither of these two functions, if used to reference cell A1, returns the #REF! error; however, neither one updates the cell reference within the formula. So, in this example, if you cut cell A1 and paste it into another worksheet, cell A2 still references cell A1 and displays a value of 0.



MORE INFORMATION
This behavior occurs if you move cells on top of the cells that are referenced, either by dragging the new cells over the referenced cells or by using the Cut and Paste commands. This behavior also occurs if you simply delete the referenced cell.

Additional query words: XL9

Keywords: kbprb KB182191

-

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

© Microsoft Corporation. All rights reserved.