Plotting a dynamic amount of series on a chart

[PeepDeep]

Hi,

Long time lurker first time question poster here

I'm trying to plot a variable amount of series on a chart.

I've created a dummy environment with data for my issue, however in the actual issues case, the data table in B29:R47 is filtered from a master on another sheet which I cannot amend. Ideally using the drop down in C3 the user selects which column from the data table they want to see the sales amount for, Level 1, Resource Type or Area. The table uses the FILTER formula and SUMIFs to populate dependent on the drop down. Finally this is then plotted.

Where is gets difficult is that each view has a different amount of unique items, Area has 10, resource type has 7 and level 1 has 3. I've tried to use OFFSET/Named ranges but this doesn't seem to work with a scatter plot/group of series and so I'm left with only some of my series plotted, or several blank ones.

Is there a way to avoid excel plotting an empty series?!

Huge thanks in advance

Test.xlsx
	C
3	Level 1
Groceries

Cells with Data Validation
Cell	Allow	Criteria
C3	List	=$B$29:$D$29

 

[BSALV]

you can hide the empty series by hiding their rows or columns or you can select/deselect them in the ribbon for your graph.

 

[PeepDeep]

Thanks for replying - the rows that need hiding/deselecting change each time a different option from the drop down is selected though.

Perhaps will write some VBA to hide blank rows automatically in my table though.

 

[Jon Peltier]

It's been several weeks, but maybe this will help.

You can select the chart, and click the funnel icon floating next to the chart. This lists the series in the chart and, if it isn't a scatter chart, also the categories (X values). You can uncheck any series to hide it in the chart without worrying about hiding rows and columns.

You could write some VBA to check the chart for empty series and hide them. Here's the filtering code:

    ActiveChart.FullSeriesCollection(2).IsFiltered = True  ' hide series 2
    ActiveChart.FullSeriesCollection(2).IsFiltered = False ' show series 2