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

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?

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

Learning More Each Day wrote:
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")

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

You can replace C2 in your formula with INDIRECT("C2") and it will always
refer to row 2 in column C.

Hope this helps,

Hutch

"Learning More Each Day" wrote:

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?