Selection of data for plotting graphs

[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

 

[Kelly O'Day]

Joseph:

You can do exactly what you want, however, you will need to do a little
planning and set up your data to allow the interactive selection you want.

Since your data series length is not the same for each series, the first
thing to do is create dynamic range names for each series you want to plot.

Next, you need to decide how you want to select your series to be plotted,
checkboxes or drop down selections are two options.

These links show examples of checkbox and dropdown approaches.

http://processtrends.com/pg_interactive_chart_checkboxes.htm

http://processtrends.com/pg_chart_scroll_bar_checkbox.htm


I'd suggest you post back with a more detailed description of your data sets
so that we can give you better advice before you jump in.

What are the X and Y axis variables? Are the data ranges comparable for the
data sheets? Is it possible to have the Y values all on one sheet? Do you
need a line chart, can you use an XY chart?

Kelly O'Day

 

[joecrabtree]

Joseph:

You can do exactly what you want, however, you will need to do a little
planning and set up your data to allow the interactive selection you want.

Since your data series length is not the same for each series, the first
thing to do is create dynamic range names for each series you want to plot.

Next, you need to decide how you want to select your series to be plotted,
checkboxes or drop down selections are two options.

These links show examples of checkbox and dropdown approaches.

http://processtrends.com/pg_interactive_chart_checkboxes.htm

http://processtrends.com/pg_chart_scroll_bar_checkbox.htm

I'd suggest you post back with a more detailed description of your data sets
so that we can give you better advice before you jump in.

What are the X and Y axis variables? Are the data ranges comparable for the
data sheets? Is it possible to have the Y values all on one sheet? Do you
need a line chart, can you use an XY chart?

Kelly O'Day











- Show quoted text -

Below is an example of the data I have. The time will be the x
variable, the other columns will be the y variables. I want to plot
this in a line chart. The data is in the same format in each
worksheet, but the length of the data will vary. I have four sheets of
this data, 'data1', 'data2', 'data3', and 'data4'. The graph is in a
worksheet called 'chart'. Its in this worksheet that I want to be able
to select using a drop down menu which worksheet of data I can plot
i.e. 'data1', 'data2', 'data3', or 'data4'. I don't want to plot
'data1', 'data2', 'data3', and 'data4'. at the same time. They must be
plotted individually. Any ideas? Thanks

Time Depth Height Position Length
Pressure
08:23:57 1451 21 0 2446 35900
08:23:57 1451 21 0 2867 35900
08:23:57 1451 21 0 3046 35900
08:23:57 1451 21 0 3410 35900
08:23:57 1451 21 0 3590 35900
08:23:58 1452 21 -1 3942 35900
08:23:58 1451 21 -1 4099 35900
08:23:58 1451 21 -1 4368 35900
08:23:58 1451 21 -1 4450 35900

Thanks in advance for your help,

Regards

Joseph Crabtree

 

[Kelly O'Day]

Joecrabtree:

We haven't made much progress yet. Your second post repeated your first post
request with the addition of example data. We'll need some more details to
help you.

Question 1:

You have restated your desire to use a "line" chart. An XY chart with the
line option selected looks exactly like a line chart. An XY chart gives you
more flexibility to accommodate different time intervals, so I repeat my
question about why not use an XY chart?

Question 2:

Did you look at the examples I sent? How do they compare with what you are
trying to do?

Question 3:

Can you provide a link to an example of the plot you are trying to generate
so that I can see it?

Question 4:

You have 6 variables in your example data:
1. Time
2. Depth
3. Height
4. Position
5. Length
6. Pressure

What variables do you want to plot? Time versus depth, time versus height,
time versus position, time versus length, time versus pressure, or time
versus variables, or some other combination like height versus pressure. I
can not tell from what you have described so far.

Question 5:

While you can do exactly what you want, you'll have to take several steps,
namely:

a. Use create dynamic range names for actual variable to be plotted

b. Set up user interaction to select which data set you want to plot

c. Adjust min and max X axis time scale to accommodate different time
period of your data sets.

Are you familiar with dynamic range names, Define Range Name Offset
formulas, use of check boxes, VBA?

Question 6:

To expedite the process, I'll be glad to take a crack at making the
consolidated chart if you send me your workbook.

Do you want to send me your workbook?

If yes, you can send it to kodayatprocesstrendsdotcom fix
address to e-mail me



Please respond to the specific questions so that we can have a dialogue that
works for both of us.

Kelly

http://processtrends.com

 

[joecrabtree]

Joecrabtree:

We haven't made much progress yet. Your second post repeated your first post
request with the addition of example data. We'll need some more details to
help you.

Question 1:

You have restated your desire to use a "line" chart. An XY chart with the
line option selected looks exactly like a line chart. An XY chart gives you
more flexibility to accommodate different time intervals, so I repeat my
question about why not use an XY chart?

Question 2:

Did you look at the examples I sent? How do they compare with what you are
trying to do?

Question 3:

Can you provide a link to an example of the plot you are trying to generate
so that I can see it?

Question 4:

You have 6 variables in your example data:
1. Time
2. Depth
3. Height
4. Position
5. Length
6. Pressure

What variables do you want to plot? Time versus depth, time versus height,
time versus position, time versus length, time versus pressure, or time
versus variables, or some other combination like height versus pressure. I
can not tell from what you have described so far.

Question 5:

While you can do exactly what you want, you'll have to take several steps,
namely:

a. Use create dynamic range names for actual variable to be plotted

b. Set up user interaction to select which data set you want to plot

c. Adjust min and max X axis time scale to accommodate different time
period of your data sets.

Are you familiar with dynamic range names, Define Range Name Offset
formulas, use of check boxes, VBA?

Question 6:

To expedite the process, I'll be glad to take a crack at making the
consolidated chart if you send me your workbook.

Do you want to send me your workbook?

If yes, you can send it to kodayatprocesstrendsdotcom fix
address to e-mail me

Please respond to the specific questions so that we can have a dialogue that
works for both of us.

Kelly

http://processtrends.com









- Show quoted text -

Ive sent you an example workbook with more detailed info.

Thanks

Joe Crabtree

 

[Kelly O'Day]

I have posted a workbook that provides the requested interactive user
selection of sheet, variable, start time, time increment.

This example uses dynamic range names. Indirect function is used with he
Offset formula to allow dynamic sheet names based on user selection of data
sheet.

The sheet list is updated with a macro.

The min X axis is selectable with scroll bar. User has drop down selection
for time interval.

http://processtrends.com/toc_chart_doctor.htm

Kelly O'Day
http://processtrends.com