protect formulas in locked cells from changing if data is moved


C

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?
 
Ad

Advertisements

J

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")
 
C

Cleve

Thanks, but it is a closed workbook.

John C said:
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")
 
Ad

Advertisements

C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top