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...
--
smither fan

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 FormatCellsProtection. Uncheck "locked"
and OK.

Select the cells you wish to lock and FormatCellsProtection. Check "locked"
and OK.

Now ToolsProtectionProtect 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

On Mon, 18 Sep 2006 14:14:01 -0700, Ross wrote:

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...

Gord Dibben MS Excel MVP

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?
--
smither fan


"Gord Dibben" wrote:

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 FormatCellsProtection. Uncheck "locked"
and OK.

Select the cells you wish to lock and FormatCellsProtection. Check "locked"
and OK.

Now ToolsProtectionProtect 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

On Mon, 18 Sep 2006 14:14:01 -0700, Ross wrote:

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...

Gord Dibben MS Excel MVP

Ross

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

But check out the list of options you can allow under ToolsProtectionProtect
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

On Tue, 19 Sep 2006 08:27:01 -0700, Ross wrote:

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?

Thanks for the help, Gord. I'll check out the post and the options.
--
smither fan


"Gord Dibben" wrote:

Ross

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

But check out the list of options you can allow under ToolsProtectionProtect
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

On Tue, 19 Sep 2006 08:27:01 -0700, Ross wrote:

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?