prevent changes to cell references when copying and pasting

[Guest]

I have an Excel workbook where cells in a column in Sheet 2 refer to the
corresponding cell in Sheet 1. However, when my colleages cut and paste data
in Sheet 1, it creates #ref errors in the second sheet where the formulae
adapt to the moving cells. There must be a way where I can lock the formulae
so when data is moved out of a cell, the formula doesn't "follow" it. Any
ideas? Thanks.

 

[Earl Kiosterud]

The only way to prevent cell references from changes from changing when the target cells are
moved is via INDIRECT.

= INDIRECT("'Sheet1'!A1")

Note that the cell reference must be inside the quotes. Don't use =
INDIRECT("'Sheet1'!"A1). The apostrophes are there in case the sheet name contains
spaces -- they can be omitted if it doesn't.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Dave Peterson]

If you don't want the formulas to adjust, copy them as text:

Select the range to copy
edit|Replace
what: = (equal sign)
with: $$$$$=
replace all

Now do the copy|paste

And then a couple more edit|replaces to change $$$$$= back to = (in both the
original range and the pasted range).

 

[Guest]

Brilliant - thank you

The only way to prevent cell references from changes from changing when the target cells are
moved is via INDIRECT.

= INDIRECT("'Sheet1'!A1")

Note that the cell reference must be inside the quotes. Don't use =
INDIRECT("'Sheet1'!"A1). The apostrophes are there in case the sheet name contains
spaces -- they can be omitted if it doesn't.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...