Data Selection for graph plotting

[joecrabtree]

To all,

I have a workbook with multiple worksheets labelled data1, data2,
data3, and data4. I also have one sheet called 'graph'. On worksheet
labelled 'graph' I have a line graph that currently plots the data in
sheet 'data1'. however what I want to do is as follows:

Add a dropdown menu to sheet 'graph'

Use this dropdown menu so that I can select which data is displayed in
the graph. i.e. data from 'data1' or data from 'data2' etc. The format
of the data i.e. column headings and layout is the same for each
worksheet. The length of the data is not the same however.

Does anyone have any ideas/a fix for this?

Thanks in advance for your help.

Regards

Joseph Crabtree

 

[Barb Reinhardt]

This is one way:

1) Set data validation with the sheet names included in it.
2) Set up a named range for your "Generic Chart_Labels" and your
"GenericValues"
3) Set up named ranges for the ChartLabel and Values for each worksheet
4) Create a worksheet change event for your "Graph" sheet so that when the
data validation cell is changed, the "Generic Chart_Labels" and "Generic
Values" used in the master chart are changed.

I know this is fairly high level, so if you need assistance, let us know.

 

[joecrabtree]

Thanks for your respose.

Would you be able to give me a more detailed explanation please?

Thanks

 

[Barb Reinhardt]

I think I'd do something like this

http://office.microsoft.com/en-us/excel/HA011098011033.aspx

When you name your ranges (similar to Date and Price) in the example. It
would be best to have the names the "same" on each sheet. When you define a
sheet level named range for date, you enter Sheet1!Date in the name cell
instead of just Date. If the sheet name has spaces, you'll need 'Sheet
1'!Date.