Multiple pivot tables & slicers - The maximum number of data series per chart is 255

[jacobardar]

Hi Excel Experts!
I am running Excel 2010.
I have a raw data set consisting of about 31000 rows and 10 columns. In my columns, month, year, product details, volume, sales etc.

I have created a dashboard with 6 different charts connected to separate pivot tables, where the pivot tables are located in separate sheets. The dashboard is created with slicers (8) and the selection will provide the appropriate graphs.

My problem occurs when i clear the selection and choose a specific product area (or simply clear all). One product area contains more than 255 different kinds, and i get the 255 max dialog box will pop up on my screen multiple times

I have a few suggestions for what could be done, but I cannot figure out how to do it.

• Create a macro that autoclick the dialog box multiple times, so the end user will not see this message (However, it has to work on another computer as well, when I will email it forward)
• In the pivot tables, switch the columns and rows, but keep the chart formatted the correct way. By this, I mean having Year and month on columns, and product, strength, pack, and company on the row axis. The problem occurs when I try to switch row/column in the chart, then the pivot table will change as well. Is there a way to keep the pivot table one way, while changing the row/columns in the chart, without the pivot table changing?
• A third option could be that for example 3 sliders must be selected before it will plot it on the graph. In this way, it will limit the number of products, and be within the 255 row limit.

I am open to any suggestions, but wanted to give some of my ideas as well.

I really hope someone can help me out, cause I have been looking around for a long time for an answer, but have not been able to find one.

Kind regards,
Jacob