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, market, product, pack, company 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 all my selections and or choose a specific market. One market contains more than 255 different products, and I get the dialog box: "maximum number of data series per chart is 255". The dialog box will pop up on my screen multiple times and I have to press "Ok" multiple times.
I do not need the +255 plots showing at the same time, but in order for the dashboard to work, one will first have to click the "market" slicer and thereafter specify the appropriate products, packs, company etc.
I have some suggestions what could be the solution, but unfortunately, I don't know to do it. I only need one solution of course, so no need to answer all the questions/if any,
Is there any way I can prevent the dialog box from showing, simply disabling it or a macro auto-clicking it? - I know I cannot plot more than 255 data series per chart and I do not want to do this. The user simply has to select more criteria before the chart becomes useful.
Is it possible to make a macro/tell Excel that there must be a minimum of for example 3 slicers chosen, before it plots the graph? - I do, however, need the slicers to be updated, so when the user selects the market, the appropriate products, packs & companies shows up. This should limit the data series per chart to less than 255
One note: It has to work on another computer, when i forward the file.
I am open to any suggestions, but wanted to give some of my ideas as well.
I really hope someone can help me out, because I have been looking around for a long time for an answer, but have not been able to find one.
Kind regards,
Jacob
Bookmarks