I have a list created from a pivot table that I would like to incorporate into an effective chart. The data is the number of violations per employee by quarter. I am having trouble conceptualizing the best way to capture this information...keep in mind that this will be performed within a macro.

Here is what I am hoping to have one graph capture:

-top 5 (or 10) violating employees for Q1
-top 5 (or 10) violating employees for Q2
-top 5 (or 10) violating employees for Q3
-top 5 (or 10) violating employees for Q4

Obviously I could do one chart per quarter that illustrates top 5 or top 10 employees, but I am hoping to fit this on a dashboard with 3 other figures. I could use a clustered column/bar graph if I did top 5 employees, but i do not know the best way to trim the pivot table data into the chart data (Countifs or large?). I know it is possible to incorporate a listbox that controls the range of data selected...this sounds like it might be the best option although I am intimidated by the level of code this would require.

I know this might sound like a generic Excel question, but I am going to write the code to execute this objective once I determine the best approach.


I would love to hear your suggestions. My brain hurts after spending all day getting vba to produce 1 chart on this dashboard. Thank you for your time!!!!