Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protecting Excel Formulas
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protecting Excel Formulas
If users input data to w/sheets they do not need access to cells containing
formulae and therefore those cells should be protected and inaccessible. Protecting those cells\columns\rows should not compromise filter or sort functions -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Chris@Preston" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protecting Excel Formulas
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. "Russell Dawson" wrote: If users input data to w/sheets they do not need access to cells containing formulae and therefore those cells should be protected and inaccessible. Protecting those cells\columns\rows should not compromise filter or sort functions -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Chris@Preston" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protecting Excel Formulas
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 On Sat, 20 Feb 2010 15:29:01 -0800, Chris@Preston wrote: 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. "Russell Dawson" wrote: If users input data to w/sheets they do not need access to cells containing formulae and therefore those cells should be protected and inaccessible. Protecting those cells\columns\rows should not compromise filter or sort functions -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Chris@Preston" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting cell formulas in excel 2007 | Excel Discussion (Misc queries) | |||
protecting formulas without protecting sheet so grouping still wor | Excel Discussion (Misc queries) | |||
Protecting Formulas | Excel Discussion (Misc queries) | |||
Protecting my formulas | Excel Discussion (Misc queries) | |||
Protecting Formulas | New Users to Excel |