Selecting data to graph using a drop down box on the graph

[mataustin]

I have a large set of data, covering the performance of a number of different product lines (over 100) over a 6 month period, day by day.
I want to be able to plot a graph of the daily performance of each product onto a line graph, but would like to be able to select which product is displayed on the graph using a drop down box on the graph itself (i.e. I don't want to create a separate graph for each product, not do I want to have to manipulate the data itself)

Can anyone advise?

 

[energman58]

Sure - Easy

Insert a combo box control into your graph which allows you to select from a dropdown list of products or whatever and then use the value that returns to populate an array you use to plot your graph. You then get a dynamic graph which displays whatever you select from your dropdown. You can find the combo box on the developer tab under insert in teh controls group; you set the range for your dropdown and cell that it returns in the Format dialog box for your combo box once you have drawn it.

HTH

 

[mataustin]

Thanks for the answer, and have got the combo box to work just as you described.
What I can't do is to get the graph to only select the data based on what is in the combo box?

The graph array is currently set as:

=SERIES('OEE tracker'!$B$2:$B$23,'OEE tracker'!$J$2:$J$25,'OEE tracker'!$I$2:$I$22,1)

where column B contains the name of the product that is reflected in the combo box (that I would like to select).

How do I amend this, or am I looking in the wrong place?

Thanks

 

[Weaver]

You need to set your graph data so that the required numbers are 'loaded' into the graphed range (possibly using vlookup), rather than change the area your graph is looking at. I'm not saying that approach is impossible, just that it will most likely require VB code to achieve.

 

[energman58]

I would simply populate a vector array (one dimensional) based on the value returned by the combobox using a formula (possibly on a hidden sheet) via a lookup function and then graph that - the combobox will return a number relating to the position in the list so INDEX would seem to be the easiest approach if your data is contiguous.

It is possible to do the same thing programatically without using an itermediate array and you could probably develop a formula solution that used named ranges to select the relevant data too but all of that seems to be over complicating things especially as the alternative is so simple.