Dynamic Pivot Table - 9 Box Grid

I notice that on your grid, you have 9 slots for each category, Are these fixed or would you like them to be dynamic?

Also the layout of the grid is a bit odd. It doesn't go in order. In other words, I was expecting to see 1-3 going across then 4-6 under it, then 7-9 under that. Or 1-3 going down column A, 4-6 going down column B, etc. However, I suppose you have a reason for organizing it this way.

I went with the assumption that you wanted it to be dynamic.

I converted the data into an Excel table mainly because they are dynamic - when you copy in your real data, all the formulas and the pivot tables will adjust to the new "size" of the data. Also, it's easier to write formulas using table references.

So the pivot table's new source is Table_Data.

Since you have Excel 2010, you also have slicers. Slicers not only look cool, they can also be used to synchronize multiple pivot tables at once. Otherwise, this would have to be coded. I have some pivot tables on the parameter sheet that get set to the same filters as the slicers. They also do a further filter for the score. The code runs these in sets of three. I also have a lookup "table" that tells me how many of each score I have in the data. If it's zero, I can't set a filter for it and I don't want to process it in the code. The Parameters sheet is used for "scratch" calculations like getting the title for each section and can be hidden if desired.

The report is fired off when any of the filters is changed on the pivot sheet. It doesn't matter if you use the slicers or the actual pivot table filters.

The final piece of code provides the titles.