dwhite30518,
Attached is a modified version of your Report Charts workbook. This workbook is VBA free and uses a Data Validation list to update the chart.
I removed the pivot tables for this particular scenario because I don't think they were working out quite the way you wanted them to. Instead, I have some index/match/countif formulas to pull the unique items list and their failure counts.
I pulled the list of unique items from the Items List. This is performed by using this formula in the 'Summary' worksheet, cell G2 and copied down to G61:
=IFERROR(INDEX(A$2:A$3572,MATCH(TRUE,INDEX(COUNTIF(G$1:G1,A$2:A$3572)=0,),0)),"")
Note: To get them to be listed alphabetically, I sorted on column A then on column B in the Summary worksheet.
Becuase of the dynamic named ranges and that this formula is copied down to row 61, it will automatically pick up new unique entries as they are added to the Items list in sheet 'Summary', column A. (To keep the unique list in alphabetical order, you would add items to the end of the Items list, and then do a Sort, first level on Items (column A), next level on Actual Failures (column B)).
Next I pulled the unique list of Actual Failures. This is performed by using this formula in the 'Summary' worksheet, cell H2 and copied down to H61:
=IFERROR(INDEX(B$2:B$3572,MATCH(TRUE,INDEX(COUNTIF(H$1:H1,B$2:B$3572)=0,),0)),"")
Same notes apply to this as to the unique item list.
Now that we have these unique lists, I created a named range called ItemList with this formula:
=OFFSET(Summary!$G$2,,,ROWS(Summary!$G:$G)-COUNTBLANK(Summary!$G:$G)-1)
I used that as the data validation list for the 'Charts' sheet cell B2.
Next was to get the failure counts. These are dependent on what is chosen from 'Charts' sheet cell B2. In the 'Summary' sheet cell I2 and copied down to I61 is this formula to get the counts:
=IF(OR(H2="",Charts!$B$2=""),"",COUNTIFS($A$2:$A$3572,Charts!$B$2,$B$2:$B$3572,H2))
Now that we have the counts, we can rank them to get the top 3 values. The rank formula is in 'Summary' sheet cell J2 and copied down to J61:
=IF(I2="","",RANK(I2,$I$2:$I$61)+COUNTIF($I$2:I2,I2)-1)
Now we have identified what the top failures are for any given item. We can extract this information to the Charts page so that we can clearly see what the top failures are. In 'Charts' sheet cell A6 and copied over and down to cell B8 is this formula:
=IF($B$2="","",INDEX(Summary!H$2:H$61,MATCH(ROW()-5,Summary!$J$2:$J$61,0)))
That range is what the chart is created from. Now we can select an item from the data validation drop-down list, and the chart will automatically update to show the top 3 failures of the selected item.
Bookmarks