11. Use direct references to unprotected cells on a spreadsheet intended to be used as a form for general use. If you think a protected cell on a protected Excel Worksheet cannot be changed, you will learn otherwise when someone enters a value in the wrong unprotected cell and chooses to cut and paste it to the correct cell. At best, the form will suddenly show all kinds of errors and be obviously unusable (unless the person knows Excel well enough to press Ctrl+Z before a save or use of a form control). At worst, the form will produce incorrect results with no indication that something might be wrong (this can be very bad indeed).

Protected or not, Excel automatically updates references when referenced cells are moved; and cutting and pasting one referenced cell over another always replaces references to the destination cell with "#REF!".

As long as (spreadsheet) size doesn't matter, the best way to do this is probably to duplicate an area of unprotected cells in a (later to be) hidden, protected adjacent area, and to make all references to entered data using references to that protected, hidden area. The cells in the protected, hidden area copy the unprotected entries using indirect references to the unprotected data.

Unless a form needs very few "protected" references to unprotected cells, using indirect references to the unprotected data everywhere can make the form extremely difficult to change (e.g., update) or debug. You can use a computed OFFSET reference to copy the unprotected data to the hidden, protected area. It's less messy than computed INDIRECT references; but you should design the computation so it will allow moving the two areas relative to each other.

Even though using the above technique will prevent users from damaging the function of the form, be aware that it will not prevent them from making changes to the form that you don't want. Formatting is disabled on a protected Worksheet, but the formats of unprotected cells on that sheet are not protected. If the user copies a protected cell to an unprotected cell, the formatting of that protected cell will be copied, too; so if you put a lot of effort into the appearance of input cells, your effort may be in vain.

Can anyone think of any reasonable use for a protected form with unprotected cells that would make this behavior appropriate? Unfortunately, as bad as it is, Excel is better in this respect. Office Libre behaves similarly, but is less likely to clearly show that the form has been damaged. For example, if the original protected equation is =A1+A2+A3, and the user cuts and pastes A2 to A1, Excel will change the (not) "protected" equation to =#REF!+A2+A3 (an obvious error), whereas Office Libre will change it to =A1+A1+A3 (the wrong answer).