Formula / Function - to hide a certain worksheet from a certain group of people

[visshukaka]

I have a sales team who provides me certain data in the template which I created. As per policy, a sales data of one person should not be shared with another person; due to which I collect data (in the excel template) from almost 100+ sales persons, then I need to put that data in to a big workbook that I created, and have seperate worksheet for each sales person. I manually have to key in all the data and then create a summary sheet which provides all the sales data on to one workbook that my sales director need to see every month.

This is heavy manual work, and I am hoping to create some thing which can be shared but not every thing visibile to every one. Say I have sales agent A, B & C ( and rest of 97+ ), I want to share my master workheet thru our online shared drive, where say sales agent A can only see his worksheet (tab) and not any other worksheets (tab) meaning even if "A" click on say workheet of B and a summary sheet whihc have data for all he should not see anything on those sheets. But when "B" click on his worksheet he can only see his worksheet and not other's. Hope this is helpful for backgound.

Now I can protect a workshhet, also I can color all cell as white, so on so forth. the problem is even I do that the data is still visible meaning "A" can click on "B"s worksheet and using "ctrl+a" he can still see what is in there. I want to protect that from happening, basically "A" should not able to click and got on to "B" s worksheet.

Is there a easy way (not a VBA) to do this?

[TMS]

Excel is NOT a secure environment. There is no way to safely include all data in one shared/distributed workbook. Do so at your own risk. Anyone with a little knowledge, a desire and access to the internet can work out how to access "hidden" worksheets and data.

The way you are doing it is probably the only way to maintain the integrity and security of your data.

I would suggest you research a macro to open open all the files in a specific folder ... and then store your salesmen's files in that folder. Open each file, extract the information, store it in the master file. Rather than have one worksheet per salesman, I would advocate a single raw data file where you structure ancd collate all the data. Done properly, you can sort it, filter it, create Charts, Pivot Tables, etc. IMO, much more flexible and adaptable.

Regards, TMS

[visshukaka]

I agree, but data integrity will not be a big issue as it's not a public information, and if the sales agent really wants to sneak into other's worksheet, then that is fine too, I just don't want to give them that functionality from my side hence would like to lock in first place. Also, my sales agent are so occupied with their targets that I don't expect they will spend any time on others work. I am opposed to Micro and VBA b/c every one are not smart enough to save their worksheet as micro enabled. So, Micro / VBA is out of the question. Any other thoughts?

[TMS]

Any other thoughts?

No. IMO, you can't hide/unhide worksheets without VBA.

[Pete_UK]

It would be YOU who would have the macro-enabled workbook. The macro could include the facility to produce a (static) copy of the master sheet along with another sheet for the sales rep, and to do this for all 100 sales reps. You could also include the facility to email the appropriate copy out each sales rep, and those copies would not have the macro included.

Hope this helps.

Pete

[visshukaka]

Thanks Pete / TMS

[TMS]

You're welcome. Thanks for the rep.