Changing graph axes using named range

[simon.stewart]

Hi,
I have a large table of data, each column is defined as a range. What
I would like to do is use the drop down list (using data validation)
where each option in the list is a named range, to change the graph's
x and y axes.
For example, if I were to select the named range for column C as my x
axis (using the drop down list) and column F as my Y axis, I would
like the graph to automatically plot this without going into the
=SERIES(<legend entry>,<X Values>,<Y Values>,<PlotOrder>)
formula and changing the named range manually. I have tried to
reference the cells in this formula where I have written the name of
the range's in them but it doesn't work.
Is there a macro I can make or is it easier than that?
Thanks a lot!
Simon

 

[Joel]

You can use a worksheet change function like the one below. I added my drop
drop down list in cell A1

Sub worksheet_change(ByVal target As Range)

If (target.Row = 1) And (target.Column = 1) Then

Sheets("sheet1").ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = target


End If

End Sub