Role based data sharing in Excel (version 365/2021)

[SRana]

Hello experts,

I have an excel file which contains data of Jan'23 till date sales across products done by various sales managers. the data contains many columns including one for employee id (alphanumeric). the data contains columns of employee name, his/her supervisor, department head, and area/region they belong to.

1. I want to distribute this data at employee level, however I want that an employee is able to SEE ONLY HIS/HER DATA and no one else's.

2. I want to share this data at supervisor level also but again what is required that the supervisor can see data ONLY belonging to SET OF EMPLYEES TAGGED UNDER HIM.

3. Want to ensure the above two while sharing the entire data/table with some protection features so that nobody is able to edit/change the data.

Please help in resolving this issue.

 

[Jeffrey Mahoney]

Just thinking it out on paper . . .

Anybody smart enough is going to be able to duplicate your formulas, change the name, and get access to other's information. You would have to apply new ID's to each employee and Supervisors. This way a person would have to know the ID's to get the data. Each person would know their ID, but nobody else's. Make a random number generator with 10 digits. You could have a lookup table on a very hidden sheet in a linked workbook. The sheet showing the data would be protected and formulas hidden. That would work

The other way is to create a macro that copies a sheet for each employee into a new workbook and values out the formulas. Same for the Supervisor version. If you set up those sheets exactly how you want to present them with formulas looking at the main database and copy them to values each time you wanted to disseminate them into individual workbooks, it wouldn't take long at all. Very secure

Jeff