Protecting formula

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I need to have a spreadsheet that others are able to add data to but I
need to prevent them from changing the forula cells.

How do I do that?

Thanks,

Robin Chapple
 
Hi Robin!

It's a two stage operation.

First decide what cells the user is allowed access to
Select those cells and then use:

Format > Cells > Protection
Remove check from "Locked"
OK

Next impose protection using:

Tools > Protection > Protect Sheet
Give and confirm password [optional]
OK

But remember that internal protection in Excel is very easy to
circumvent so make sure that you have a good backup you can use if
someone gets in and stuffs up your formulas.
 
-----Original Message-----
I need to have a spreadsheet that others are able to add data to but I
need to prevent them from changing the forula cells.

How do I do that?

Thanks,

Robin Chapple
.
Click to expand...
You should protect the worksheet. Here's how...

Select the cell(s) you want people to be able to edit

Go to "Format", "Cells" and go to the "Protection" tab

Uncheck the "Lock Cells" box (do this for all cells you
want people to be able to edit)

After you unprotect all cells you want to allow editing
to, go to "Tools", "Protection", "Protect Sheet"

Uncheck "Select Locked Cells"

You can choose whether or not to use a password

To unprotect the worksheet just follow the same
instructions, in reverse
 
Hi Robin!

I agree that it isn't obvious but if you think about it, it is a
pretty logical way to do it. For just a few cells, a one stage process
might be possible but if you're using protection for lots of different
cells it would be a real PITA to use a one stage operation that needs
repeating each time that you want to change protection status.

Again, note that this protection / security is just about suitable for
ASIO and not much more:

See:
http://www.mcgimpsey.com/excel/removepwords.html

Some code JE and I upgraded in the slow period after Chrimble in 2002.

Which of the four seasons do you have today <vbg>
 
You could download the third party tool: AddinTools Assist from
http://www.addintools.com. It will temporarily display unlocked cells and
locked cells with different background colors. You will find out which are
locked cells, which are unlocked cells immediately. The "To Lock" button and
"To Unlock" button could lock or unlock the selected cell immediately. In
this way, you can decide fast which part of spreadsheet allows changing, and
which part disallows.
The function is very useful when you have forgotten which cells are locked
in a workbook or sheet.
 
Back
Top