Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Toshiba
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Arvi Laanemets
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
protecting the worksheet Jezebel Excel Worksheet Functions 1 December 4th 05 09:15 AM
Macro to search for and display data in another worksheet Mark H Excel Worksheet Functions 0 June 14th 05 12:40 PM
Protecting a Worksheet but ability to Sort Toni Excel Discussion (Misc queries) 3 March 8th 05 10:41 PM
Copy from worksheet to another x times Union70 Excel Discussion (Misc queries) 0 March 7th 05 10:03 PM
Weekly Transaction Processing Ralph Howarth Excel Worksheet Functions 4 January 19th 05 06:37 AM


All times are GMT +1. The time now is 03:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"