Hi excel world,

I have a database in excel and I'm created a way to graph data the way I need. I have a separate tab for my graphs, and index the information in based on the name of the thing I'm analyzing. So, I can choose the item I want to see a graph on from a dropdown menu and drops in perfectly. Excel graphs my datapoints, and I added the average as well - so it graphs two lines (the average is indexed into the page as well, though I don't think it really matters).

Then, I wanted to be able to put 2 items on the same graph (plus the average as before). The only way I saw fit was to create a second tab, duplicate all the graphs, and then add a second drop down menu. I added the drop down menu and indexed all the cells in on the same page as the first item drop down. I did this for 5 pages - to have the ability to graph 5 items and the average on a single graph. To do so, I just select an item from each drop down menu and go to the tab "5 Item Graph" and it has everything I need graphed.

It works fine, but I'd like to think of a way where I could get all of this into 1 tab on 1 set of graphs. I tried added an item named "None" with no information, but (A) this messes up the max and min of the graph plot area (which I could manually fix but would rather not becasue lazy), and importantly (B) it leaves a new series named "None" on the legend. Is there anyway I could make a graph where I could add and delete series on one page with named ranges?

Ideally, I'd want to have 5 drop down menus and have a graph setup where if I choose an item from 1 graph, it ONLY shows that one item's data series and the average and completely ignores the other dynamic ranges that are already written into the graphs data range. But also, if I pick 5 items, to where it would give all 5 items and the average on the graph. I just have no idea if excel can completely ignore a data range even if all the values are 0 (which is why it still leaves the series name on the legend).

Assume the min/max doesn't matter and I can manually fix that, as I already have a macro that does this for me.