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)
: 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