Protection on excel file

R

Ross

To the 'community'

Is it possible to protect a formula and still have it update the calculation
when others add to the database?

Thanks ahead for your help...
 
G

Gord Dibben

Ross

Yes the protected formula will calculate.

By default all cells are locked when the sheet is protected.

Hit CRTL + A(twice if xl2003) then Format>Cells>Protection. Uncheck "locked"
and OK.

Select the cells you wish to lock and Format>Cells>Protection. Check "locked"
and OK.

Now Tools>Protection>Protect Sheet. This is mandatory!!

You can set a password to unprotect. These can easily be broken in Excel but
will keep your formulas from being accidentally overwritten.


Gord Dibben Excel MVP

To the 'community'

Is it possible to protect a formula and still have it update the calculation
when others add to the database?

Thanks ahead for your help...
Click to expand...

Gord Dibben MS Excel MVP
 
R

Ross

Hi Gord,

I wasn't able to get it to work. Everything was fine until I protected the
sheet. When I tried to input more records using the dataform, I got the
message "Cannot extend list or database". Also, I lost the ability to sort
the database, as 'Sort' is grayed-out on the menu, and the Sort
Ascending/Sort Descending buttons are also grayed-out.

Any ideas?
 
G

Gord Dibben

Ross

There are many things you cannot do on a protected sheet.

But check out the list of options you can allow under Tools>Protection>Protect
Worksheet............if you're running Excel 2002 or 2003

Have a look at "allow users to edit ranges" also

Generally for sorting, filtering and that sort of operation you would use a
macro to remove protection, do the work, then re-protect.

Browse through the results of this google search for posts by Dave Peterson on
"userinterfaceonly"

http://snipurl.com/wnzy


Gord
 
Top