Protecting a worksheet with filters

[Guest]

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.

 

[Guest]

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

 

[Arvi Laanemets]

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 Format>Sheet>Unhide, 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