Is there a way to dissallow users from deleting formulas (and data)in a column

[Dave K]

Does anyone know whether there is a preferred way to dissallow users
from deleting formulas (and data) in a column?

I have a shared document that contains formulas in certain columns. On
occasion, users will likely accidentally delete the contents of those
cells (thus eliminating those necessary formalas). Is there any way to
prevent them from doing so?

Thanks.

 

[Dave Peterson]

You can lock the cells and then protect the worksheet.

But there are lots of things that aren't available when the worksheet is
protected.

 

[hall.jeff]

You can lock the cells and then protect the worksheet.

But there are lots of things that aren't available when the worksheet is
protected.

actually, most of the things that you'd want to still allow can be
"turned on" when you do the protection... lock the cells and then goto
tools->protect sheet and see all the choices. You may want to check
everything on the list. Excel is smart enough that it won't allow you
to delete a column with a locked cell or anything like that...

 

[Dave Peterson]

"Most" doesn't really apply -- well, to me anyway.

If there's one important thing that I can't do, then it's enough to not want to
protect the sheet.

 

[hall.jeff]

For you and me that's probably accurate. For internal users of a
workbook I find that only the rarest of corner cases is not covered by
the defaults.

 

[Dave Peterson]

You ever use data|subtotals or data|group? Lots of people do and want to expand
contract rows whenever they want.

 

[Anon]

A couple of techniques I use:


1: Shade boxes light blue that require user input. Yellow boxes for output
(formulas)

2: Protect the cells that have formulas. Unprotect the cells that require
user input

3: Hide cells (rows or columns) with formulas)

4: Turn off display of formulas when needed.

5: for cases where you want open input- for example entering a temperature
either in C or F to get the opposite, I have used slider controls so both
cells, (°F and °C are changed simultaneously, so the user can slide away
without ruining either formula).

Hope this helps.
Paul