Protecting formulas: enable editing and deleting the cell’s value?

[mika_h]

I have several cells with formulas (and listboxes) and my users are working on the sheet
I want them to be able to delete the values in the cells, but without deleting the formula (e.g. user selected a value from the list, then decided he made a mistake and wants to delete the value. Right now, doing so will delete the value and the formula).
Is there any way to lock the formula, but enable editing and deleting the cell’s value?

thanks

[zbor]

How do you mean?

Value is determined by formula. If you want to delete value, formula should calculate same each time. For example your formula say: =A1+1 and result is 3

Whenever you delete number 3 formula should calculate it again.

Solution is to control input. So change A1 value.

[teylyn]

Hi,

you need to protect your formula cells and leave the data input cells editable. To do that, for each cell you want the user to edit, open the cell format dialog, click the Protection tab and unselect the "Locked" tick box. (Cells are locked by default, so you only need to unlock the ones for data entry.)

Next, you need to protect the sheet: In XL 2007, right click on the sheet tab and select "Protect Sheet", confirm with OK

Now your users can only enter values in the cells you unlocked. If your formula cells are still locked, they will not be able to delete anything in them.

Next, you have to educate your users and tell them that if they want to correct a calculated result, they can not edit the result itself, but rather have to edit the cells that are the input for the calculation.

cheers