Plot graph depending on drop down box selection

[joecrabtree]

All,

I have a drop down box that allows the user to select from a list of
worksheets in the workbook. The selection returns a value equal to the
name of the worksheet. There are multiple worksheets with data sets.
Each data set is identical i.e. two columns in A, and B. The lengths
of these data sets could vary.

I have an x y scatter graph in a summary worksheet. How do I use the
dropdown box to select which data (worksheet) is plotted in the x y
scatter graph?

Thanks in advance for all your help,

Regards

Joseph Crabtree

 

[Peter T]

Without code -

Create similar Worksheet level (local) names for the data on each sheet, eg

Sheet2!DataX refersto A1:A8
Sheet2!DataY refersto B1:B8
(change to correct range on each sheet)

Lets say the dropdown with list of sheet names is on Sheet1, in cell D2

Create two named formulas (Workbook/global level)
Xvalues refersto =INDIRECT(Sheet1!$D$2&"!DataX")
Yvalues refersto =INDIRECT(Sheet1!$D$2&"!DataY")

(ensure you've got those absolute $)

Create a chart with the source on any of the data sheets, format as
required.

Select the series and change the series formula as follows

=SERIES(,Book1!Xvalues,Book1!Yvalues,1)
or maybe
=SERIES(Sheet1!$D$2,Book1!Xvalues,Book1!Yvalues,1)

where Book1 is the name of the workbook

Regards,
Peter T