Hello - I am wondering if I can get some guidance on how to set up a Dashboard for different users to see only their specific groups. We have Trainer Team Leaders with a small team working under each of them. We need the corporate office to see ALL information but then we need the Team Leaders to only see their specific team information.
I prepared some dashboards with slicers and I am wondering if I need to set up a separate page for each Team Leader that only points to the specific team members and then set up a login page so that each team leader would sign in and then only their own pages would appear for them to view the graphs and run pivot reports. Since there are 3 or 4 dashboards and 5 Team Leaders, that would mean that I would have to create 20 separate dashboard tabs and then build VBA code to have the logins pull certain tabs in play for each of the Team Leaders.
Is there a way in Excel that I could share the one file with the 4 main dashboards and then have each Team Lead only have access to the slicer with their name on it and they cannot click on anyone else's name and see other teams. Currently my Excel dashboard can be viewed by everyone for all information...and I need to be able to have each Team Leader to only have access to their team's information and drill down only on their own information. Is there a way to have user-level access on slicers?
I have attached a copy of the dashboards I created if that is helpful.
- The first tab (Summary) can be viewed by ALL users.
- The other 3 tabs, we would need the user-lever security set up so that the 5 different Team Leaders would only see their specific team information and not everyone's information.
Please let me know if this is possible. Also, is it possible to have this information published (with all the security) through the Web App? Do you have a suggestion on how best to publish the information if not through using Embedded Web App version?
Thank you in advance for your help and advice.
Have a great day!
Annalisa
Hi Annalisa,
No file attached. You need to click the yellow Start Upload button after selecting your file and then wait for the grey check mark beside the file size to ensure it has completed uploading.
That said, you can't use Slicers for this functionality. Also, Excel cannot guarantee to secure sensitive data, even with passwords and other built in protection. The best you can do is make it difficult for users to access. This post explains how to achieve what you want within Excel's limitations using the Excel Web App. Hope it gives you some ideas.
Mynda
Hi Mynda,
Thank you for your response. The idea of using the Excel Web App came from the link of the post you sent me...so thank you again for that great idea! 
Although I couldn't find how your user and password to pull in the specific user views. When you enter a user name and the corresponding password, where is that view pulling from? Do you have a sheet for each of the users and is there a code built in to pull in that specific information?
I was able to follow that you set up the password & validation but I'm just wondering if you could share how it's pulling the user specific information when you enter the name and password. I went through the steps provided by the post, but I still couldn't see what was done to get the page to only show up user specific info. If you could provide me some guidance or suggestion on how this was done, I would be greatly appreciated.
I will try to upload my report again - my apologies, but it seemed that there was an error uploading after I hit send - it's probably user error on my part, so I apologize for the inconvenience.
I also want to let you know that all your posts and videos have been really helpful and I am learning lots from all your tips and tricks of Excel & Dashboards.
Thank you again, and hope to hear from you soon with regards to getting the specific user views to display.
Have great day!
Annalisa
Apologies, here is my attached file.
Hi Annalisa,
Thanks for sharing your file. I cover this technique in my Excel Dashboard course in detail.
The way the password works is every value displayed in a chart/table in the dashboard is multiplied by the result of the password formula in cell E2 on your Validation sheet.
When you multiply by TRUE it is the same as multiplying by 1, therefore the formulas evaluate correctly. And multiplying by FALSE is the same as multiplying by 0, which results in the values being hidden.
Therefore, you have to add *Validation!$E$2 to the end of the formulas that display in your charts/tables. This will either hide the results or allow them to display. i.e. you cannot link charts directly to the PivotTables. You have to extract the data from the PivotTables to another set of cells where you can add *Validation!$E$2 to the end and then reference those cells in your charts.
Hope that points you in the right direction.
Mynda
P.S. some other tips. Change the Estimated Cost by First Nation Name chart to a bar chart with the values in descending order from top to bottom. Remove 3D and shading effects from charts - this only distorts and detracts from the message. If Pie charts have more than 3 segments, use a bar chart sorted in descending order.
