Select chart series using dropdown list

[Alex Delamain]

I often want to look at XY plots of various combinations of data. What
would like to be able to do is simplify (and speed up) the process b
being able to select the x and y series using dropdown lists.

ie: I import the data (varying rows and columns) into a sheet and the
use Insert Name Create to turn each column into a named range.

On a new worksheet I use Insert Name Paste to give a list of the rang
names from which I can then select my X and Y ranges using drop downs


The question is - how can I set up an XY chart so it takes its rang
names from the dropdowns

 

[Alex Delamain]

Well - I have found a way that may not be elegant but it works!

What I had to do was import the data table and then create a lookup
table showing the column headings with a second column showing the
column number (col A = 1, col B =2 etc)

Using data validation I created the dropdowns to select the X and Y
axis variables. I then used the lookup table to convert the variable
name in the drop down into a column number.

Finally I set up named ranges xaxis and yaxis using the column numbers
to offset from column A.

If anyone can show me a simpler or more effective way I would love to
know it.

 

[Jon Peltier]

I wrote about this:

http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______