Protecting Excel Formulas

[Chris@Preston]

Can anyone help with the protection of part of an Excel worksheet (Excel
2003)whilst still allowing sorting and filtering of data?
Individual users paste data into the worksheet and, despite comprehensive
instructions, sometimes manage to delete / overwrite existing formulas. The
individual users need to sort and filter the whole worksheet after their
"input" so the basic procedure of locking and protecting the cells containing
the formulas isn't an option. Any help would be most welcome.

 

[Chris@Preston]

Many thanks for the prompt response. Please accept my apologies but I think
I have mislead you somewhat by my description of the problem. The individual
users input data to their own worksheets then copy and paste part of their
own worksheets to a master worksheet in a separate workbook. (As well as the
data they input their own worksheets contain formulae which must not be
copied to the master worksheet). It is the master worksheet in the separate
workbook where the problem occurs. The master worksheet has some 46 columns
for data entry followed by 40 columns of formulae and the users seem to
either manage to copy and paste more of their own worksheet than is required,
thus overwriting some of the cells containing the formulae, or somehow delete
the formulae from the cells. If I protect the cells containing the formulae
in the master worksheet the users can paste their data with no problem but
the sort and filter functions on this worksheet are automatically disabled
and I need the individual users to have access to both of these functions
once they have pasted their data.

 

[Gord Dibben]

Your basic design flaw is allowing users to randomly select data to copy and
paste to Master.

You have to take that out of their control and put yourself in control of
what data to send to Master.

The selection process should take the form of VBA code which selects data
based on some criteria then pastes to Master at a designated range.

Do not rely on users to get it right.


Gord Dibben MS Excel MVP