XL2000: Link Formula Returns #REF! Error Value (182191)
The information in this article applies to:
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.
| Modification Type: | Major | Last Reviewed: | 10/6/2003 |
|---|
| Keywords: | kbprb KB182191 |
|---|
|