prevent changes to cell references when copying and pasting
 

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.

prevent changes to cell references when copying and pasting
 

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...
-----------------------------------------------------------------------
"MiChaos" wrote in message
...
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.

prevent changes to cell references when copying and pasting
 

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




MiChaos wrote:

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.

--

Dave Peterson