portected cells

[John Folwell]

I have created spreadsheets with unprotected areas for data entry. The data
is used in formulas in protected cells. If the user enters data in the wrong
cell and moves it to the correct cell, the formulas are changed. Is there
any way to prevent the corruption of the formulas by the users action?

 

[Joerg Mochikun]

cell protection doesn't help you to solve your problem. Maybe you can make
your formulas more robust.
Example: Lets assume that your unprotected cells A1 and A2 contain the
values 1 and 2. Your protected cells B1 and B2 contain the formulas A1*2 and
A2*2. If the user moves the contents of A2 to A1, your formula in B1 will
produce an error. However if your formulas in B1 and B2 would read
=OFFSET(B1,0,-1)*2 and =OFFSET(B2,0,-1)*2, then moving cells in A1:A2 would
still produce correct results since the formulas point to cells positions
(which are fixed) rather than cell addresses (which are overwritten by move
operations).

Cheers,
Joerg Mochikun

 

[Joerg Mochikun]

cell protection doesn't help you to solve your problem. Maybe you can make
your formulas more robust.
Example: Lets assume that your unprotected cells A1 and A2 contain the
values 1 and 2. Your protected cells B1 and B2 contain the formulas A1*2 and
A2*2. If the user moves the contents of A2 to A1, your formula in B1 will
produce an error. However if your formulas in B1 and B2 would read
=OFFSET(B1,0,-1)*2 and =OFFSET(B2,0,-1)*2, then moving cells in A1:A2 would
still produce correct results since the formulas point to cells positions
(which are fixed) rather than cell addresses (which are overwritten by move
operations).

Cheers,
Joerg Mochikun

 

[John Folwell]

Thank you. That is certainly a viable solution. However in a large complex
spread sheet where the fill function is used extensively to create formulas
this would make creating an idiot proof application extremely tedious. Why
isn’t there another level of protection to keep formulas uncorruptable? I
can probably use your idea in a workaround. Thanks again.

 

[John Folwell]

Thank you. That is certainly a viable solution. However in a large complex
spread sheet where the fill function is used extensively to create formulas
this would make creating an idiot proof application extremely tedious. Why
isn’t there another level of protection to keep formulas uncorruptable? I
can probably use your idea in a workaround. Thanks again.

 

[Gord Dibben]

Idiot-creation far outstrips Idiot-proofing


Gord Dibben MS Excel MVP