Dashboard linked to multiple table's

[locka]

I need some help. I've been asked to create a dashboard for our customers to make it easier for them to see our monthly KPI. etc while at the same time being able to delve into more detail if needed.

This data comes from a number of sources. My KPI report, our It logging system, Email platform, Active directory etc.

What id like is to get is a Dashbloard front screen which has 2 choices one for the list of 7 organisations and second for 2 customer types they cover. With a list of charts that visualised the data to be display. Some KPI values are for all organisations and others are split between the two customer types. I also need to then be able to split the backing data down to each organisation and customer type depending on what is select from the dashboard

I initially created a sheet to hold all the calculations for the charts and on the dashboard created two slicers which picked there data from the calc sheet. I would ideally like this to then adjust those KPI where customer type can change. Along with the charts that summarise the backing data . Such number of Active inactive Ad accounts email accounts etc. Plus filter all other backing data tables(Not pivot tables).

I have number of issues which I'm struggling with:-

1. If I select an item from the slicer the associated table is filtered. I'm just not sure how to work out what was selected (It changes the row reference depending on what you select.) So that I can then pull data from relevant table and KPI sheet using formulas such as countif.
2. How to get those two slicers to filter all the other tables(Not pivot tables) to match options chosen on the front screen.
3. Are slicers my best choices because for somethings like , list of emails and there status organisation could look at both customer types together and with others such as KPi they would only be looking at either customer type. I wonder if it might be easier to restrict it to just choosing one Organisation with one customer type.
4. How to filter all the tables to match the Front dashboard choices
5. Also allow the customer to filter the backing data further by things such as Active/inactive email accounts. Without adjusting the charts on the front screen.
6. To do this with as little if any VB code

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

I'd be inclined to use Power BI for this sort of task.

[locka]

Hi AMAliGW thanks for the reply, I wish I had access to Power BI and this might happen in the future but not for now. Would love to attach spreadsheet but there a lot of data contained and would take ages for me to santaise and even then to explain some of the data like my kpi workings etc. I hoped that my explanation would help.

Perhaps the first two points would be good

1. How to find out which options have been sliced from a table and use in formula such as countif (Data!A1:700,Slicer value")