XL2000: Link Formula Returns #REF! Error Value

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

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

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

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.