Select chart series using dropdown list
 

I often want to look at XY plots of various combinations of data. What I
would like to be able to do is simplify (and speed up) the process by
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 then
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 range
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 range
names from the dropdowns?


--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
View this thread: http://www.excelforum.com/showthread...hreadid=400730

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.


--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
View this thread: http://www.excelforum.com/showthread...hreadid=400730

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/
_______


Alex Delamain wrote:

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.