how do i protect cells in a shared worksheet

D

Debi

is it possible to protect only cells that contian certain formulas within a
shared worksheet yet still have the values on those cells updated when
spreadsheet is opened due to links to other shared worksheets?
 
D

Dave Peterson

Yes.

Just do all your setup work before you share the workbook.

Create your formulas, lock/unlock the cells, protect the worksheet

Then share the workbook.
 
D

Debi

Dave when you say do my setup before I share the workbook, I am not sure I
understand or perhaps I did not give the correct information at first. I am
an auditor and therefore the workbooks I am need to protect my formulas in
have already been written by someone else is it still possible to protect
those cells of after the design and data input on the workbook. Also when you
state lock and unlock the cells then protect the sheet, the only cells I want
protected are those that I have entered formulas into those are also the
cells that are updated when opening the workbook some of the remaining cells
are accessed by other for either viewing or adding data in.
 
D

Dave Peterson

When you wrote shared workbook, I assumed you meant shared via tools|share
workbook.

If you just meant that you sent out copies of your workbook, then the sharing
stuff I wrote makes very little sense <bg>.

Cells being locked and worksheets being protected go hand in hand.

You can lock cells (or unlock cells). But until you protect the worksheet, you
won't notice any difference.

Once you protect the worksheet, then those locked cells can't be changed(*) by
the user.

You have a couple of choices.

Lock all the cells and come back and unlock the cells you want unlocked.
or...
Unlock all the cells and come back and lock the cells you want locked.

It's just a numbers game. If there's less to do one way, do it that way.

In general...

Select A1.
hit ctrl-a (twice in xl2003) to select all the cells on the worksheet
Format|cells|Protection tab
Uncheck Locked (every cell will be unlocked).

Now with all the cells still selected
edit|goto|special|check formulas
now Format|cells|Protection tab
Check Locked (all the formula cells will be locked)

You'll probably want to lock all the cells with
headers/instructions/descriptions, too. Just select as many as you can see and
Format|cells|Protection tab
check Locked
(as much as you need)

But those locked/unlocked cells behave the same way--until you protect the
worksheet.

Tools|protection|protect sheet.
(Give it a nice memorable password)

Now the sheet's protected--the locked cells can't be changed(*) by the user.

============
(*) excel's protection is very weak. It can be broken in minutes by anyone who
knows how to make it to this newsgroup -- or can search google. This security
isn't made to protect intellectual property. It's made to stop users from
overwriting formulas.

ps. If you were trying to protect the formulas from being viewed, there's an
option under format|Cells|Protection tab|check hidden

That also doesn't do anything special until you protect the sheet.
 
Top