Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protecting a worksheet with filters
I have a worksheet with multiple columns and rows that contain sensitive
data. I need this data to develop a price sheet but do not want everyone to see this data. I use the filters to make my list more viewable and hide a bunch of columns that are for my eyes only. The problem is anyone can still unhide the columns after I protect the worksheet. I haven't been able to figure out how to show just what I want and not have the other info accessable. Appreciate your help. -- Toshiba |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protecting a worksheet with filters
The only way that I can think of that would be safe, would be for you to make
a macro that would delete all sensitive information before the book is closed, then in the future whenever YOU open that book, the sensitive information could be imported (via macros) from another book that only YOU have access to......say like it's on a JumpDrive that you carry in your pocket. hth Vaya con Dios, Chuck, CABGx3 "Toshiba" wrote: I have a worksheet with multiple columns and rows that contain sensitive data. I need this data to develop a price sheet but do not want everyone to see this data. I use the filters to make my list more viewable and hide a bunch of columns that are for my eyes only. The problem is anyone can still unhide the columns after I protect the worksheet. I haven't been able to figure out how to show just what I want and not have the other info accessable. Appreciate your help. -- Toshiba |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protecting a worksheet with filters
Hi
Create 2 additional sheets, p.e. Sensitive and PriceTable. On 1st sheet (Sensitive) you have all your sensitive data. On another sheet you calculate the price table based on sensitive info. NB! On this table you must have ONLY the info, which will be on sheets available to users. On worksheet(s) meaned for users, retrieve all price info from PriceTable, using links or VLOOKUP or INDEX or SUMPRODUCT etc. Activate VBA editor. In VBA Project Properties window, set both Sensitive and PriceTable sheets to be Very Hidden. Protect the VBA project with password, and close VBA editor. An explanation. User don't see sheets set to be very hidden. And he can't make them visible through FormatSheetUnhide, as very hidden sheets don't arrive in hidden sheets list. On his worksheet, he can see formulas (unless you hide them too), which are referring to very hidden sheet PriceTable, but any link he creates, reveals only price information, but no sensitive info, and only values are returned. User don't know the name of Sensitive sheet, unless you reveal it him yourself, and so he can't use links to refer to this sheet directly. Of-cause a determined and experienced in Excel user can easily find a way to bypass all excel protections, but for ~99% users such a design is enough. Arvi Laanemets "Toshiba" wrote in message ... I have a worksheet with multiple columns and rows that contain sensitive data. I need this data to develop a price sheet but do not want everyone to see this data. I use the filters to make my list more viewable and hide a bunch of columns that are for my eyes only. The problem is anyone can still unhide the columns after I protect the worksheet. I haven't been able to figure out how to show just what I want and not have the other info accessable. Appreciate your help. -- Toshiba |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protecting the worksheet | Excel Worksheet Functions | |||
Macro to search for and display data in another worksheet | Excel Worksheet Functions | |||
Protecting a Worksheet but ability to Sort | Excel Discussion (Misc queries) | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |