protect formulas in locked cells from changing if data is moved

[Cleve]

I have locked and protected a worksheet leaving only "Select unlocked cells"
checked. The problem is that if a user "moves" an entry in an unlocked cell,
either by ctrl X or drag and drop the cell references in the locked formulae
that referred to the original position change to the new position. Is there
any way of preventing this without turning off the drag and drop
functionality?

 

[John C]

Assuming you aren't working with closed workbooks, you could always use the
INDIRECT function in your formulas, i.e:

instead of =A1+B1
it would be =INDIRECT("A1")+INDIRECT("B1")

 

[Cleve]

Thanks, but it is a closed workbook.

Assuming you aren't working with closed workbooks, you could always use the
INDIRECT function in your formulas, i.e:

instead of =A1+B1
it would be =INDIRECT("A1")+INDIRECT("B1")

 

[Cleve]

Actually I'm not sure what has happenned. I can get the Indirect function to
work provided I keep it simple, no if functions. It also returns a 0 if I
try and use the result in a subsequent formula. There is also a bigger
problem with using it, it will take ages to edit the workbook and will
increase the size by a huge amount.
Thanks
Cleve