Prevent function errors caused by cutting & paste or drag & drop

[Learning More Each Day]

I have assembled a function on a worksheet that references cells in the
worksheet. The worksheet is a living document. Changes are often made by
cutting and pasting or dragging and dropping a cell value from one place to
another, which always causes errors in the associated cells. Example: A
function may reference cell C2. If I drag (move) the contents of C2 to E2,
the function cell reference for C2 changes to the error #REF. Is there a way
to make the function reference C2 before and after contents are dragged
(moved) to a different location?

 

[Glenn]

I have assembled a function on a worksheet that references cells in the
worksheet. The worksheet is a living document. Changes are often made by
cutting and pasting or dragging and dropping a cell value from one place to
another, which always causes errors in the associated cells. Example: A
function may reference cell C2. If I drag (move) the contents of C2 to E2,
the function cell reference for C2 changes to the error #REF. Is there a way
to make the function reference C2 before and after contents are dragged
(moved) to a different location?

=INDIRECT("C2")