July 6, 2021
I have been trying to figure out how to get the information in one spreadsheet to show each of the operator's status for each of their courses in a dashboard but I can't seem to figure out a way to link the status information to the courses AND operators.
In one of the worksheets (Course Requirements), this lists the operators, the courses, and the status of each of the courses per operator. I was able to build a dashboard that gives a graph of each of the courses showing the count of the operators per status per course (tab called Participants Dashboard). And then I built a dashboard that counts the number of statuses per course (tab called Requirements Dashboard). The Requirements Dashboard was built using the worksheet called Status which I made pulling the information from the Course Requirements tab. I need to somehow link this information so that I can build a dashboard that can pull the Operators name and his or her status in each of the courses listed.
For example, I want to be able to click on the operator(s) name(s) and be able to see their status on each of the courses. Or click on the status and see which operators have that status in any of their courses.
Is this possible? I am thinking it has to do with somehow linking the two tables (Course Requirements and Status) together, but I am lost as to how or which items to link.
I have been using Excel 2016 for what I have completed so far. Any help/guidance or assistance you can offer would be greatly appreciated.
July 16, 2010
I think your source data (Course Requirements) table is in the wrong layout. You have a separate column for each course when you should have one column for the course name and then a column for the status. Then you'll be able to build the PivotTables and Charts the way you want.
This can be fixed with Power Query's Unpivot tool.
July 6, 2021