October 17, 2004

Protect Formulas

I've posted a few times about Protection (Please look here, here and here)

Here's another very quick way to (partially) protect formulas.

Go to Data, Validation and select Custom from the Validation Criteria dropdown box on the Settings tab. Then enter the following into the Formula text box.


Simple. If you try to overwrite the formula, an error message will appear like below (you can choose a different style on the Error Alert tab)

So, what's happening here? Well, ="" means "equals nothing", so if you try to enter "something" an error will occur.

However, this is not 100% reliable as it won't prevent deletion by using the Back Space or Delete Keys, and the cell can be removed by deleting the Row or Column also.

At the same time, it will prevent accidental overwriting in most cases, so it's a good protection alternative to remember. It's easy to use and every little bit helps.

Write a Comment
Hi Andrew,
I like this solution and I used many times.
Usually I write an instruction for this.
"When you accidentally delete the contain of a cell, you can undo this with the edit/undo menuitem."
Otherwise the user cannot write back the original data.
Best Regards,
Posted by Zoltan Till at October 19, 2004 00:10
Excellent idea Zoltan. The more detail shown, the easier for the user to understand!
Posted by Andrew at October 19, 2004 00:18
Hi Andrew,

I have tried your solution , but the moment I paste an invalid entry in the cell that conatins the validation , the cell accepts it.

Example:- I enter a validation in cell A5 that the number entered cannot be greater that cell C5, It shoots an error message when I type in a number greater than cell C5, but the moment I copy a number which is greater than cell C5 from a different location , the cell accepts it and the validation is over written.

Kindly advise.
Posted by Kevin Abraham at January 16, 2006 14:48