Auto-populate/generate scatter plot from selection in e.g. drop down lists

[RHEDE]

Hi,

I've attached a file as example.

The low-down is that I want users to be able to,

1) Open "Data"-sheet, and paste variable-data for multiple persons in columns U-AY.
2) Open "Results"-sheet, and select either one or a multiple set of "Factors" from "Data"-sheet (columns C-T), via e.g. drop-down lists, which then would automatically populate a scatter plot in the "Results"-sheet (based on the selection of Factors).

Any ideas? Or would it be simpler to just pre-make separate graphs for every possible combination and fetch these based on the drop-down selections (please no...)?

Thanks for your input!

[dflak]

What is it you are trying to plot? Can you give a sample chart? Also can you explain better how the selection of the factors affect the plot.

Can the selected factors be non-contiguous?

[RHEDE]

Thanks for your reply. I updated the Example.xls (attached) with sample graphs: 2 for factor-combinations, 1 for single-factor. I'm unsure what you mean by non-contiguos, but the factors should be possible to mix and match: like Factor 1 and 13, 5 and 8, etc.

[dflak]

I do not see any charts in this download.

[RHEDE]

Sigh Fixed it, thanks.

R

[dflak]

Much better

[dflak]

I have some more questions based on the examples.

I can see when you have just one factor selected you get a plot with a "generic" 1, 2, 3, 4 on the X-axis.

If you select 2 factors you plot one on the X-axis and one on the Y-axis. Do you have any rules as to which gets plotted on the X-Axis and which gets plotted on the Y-Axis?

What do you want to do if you select more than two factors?

[dflak]

I think I have what you want.

Cells B2 and B3 on the Chart Sheet let you pick the Y and X Axis. "None" is an option for the X-Axis if you only want to plot one thing.

The key to the plot are the dynamic ranges defined with the name manager. I had to include some VB code. When you change what you want to plot on the Y-axis, you loose the labels. I have a change event on the chart page to trigger code to restore them. I am plotting 4 points in each range. If more points are required, or if a variable number of points are required then the named ranges will have to be made more dynamic.

The following two articles explain how I built the named ranges and applied them to the charts.
http://www.utteraccess.com/wiki/inde...Dynamic_Ranges
http://www.utteraccess.com/wiki/inde...namic_Charting

[RHEDE]

That is simply outstanding. Thank you dflak. As to your earlier question about what goes on which axis, the X-axis should be the primary-plotted-axis (I changed this in the file you provided, no problems).

1) I do however need to be able to plot a variable amount of data points. Do you have an easy-peasy solutions for this? I'm not asking out of pure laziness (only partly ), I usually learn better when the correct solution is already in place - allowing me to pick it apart.
2) If Plot-None is selected (i.e. omitting a factor for the Y-axis) how would one remove or make the Y-axis-scale transparent? I'm guessing more vba, which is really out of my league. Is it easily doable, like the label-redeeming-code you provided?

Thanks again, your help is so appreciated.

R

[dflak]

I'm going on holiday. I'll take a look at it when I get back next week .

[dflak]

I added a bit of code to the change event on the chart sheet.

Please Login or Register  to view this content.

This code checks to see if there is a change in cell B2 or B3 and then runs the appropriate subroutine to either add labels or change the axis.

In the apply labels module I added a new subroutine

Please Login or Register  to view this content.

I didn't "invent" this code. I just recorded a macro to add and delete the axis and cleaned it up a bit. The macro recorder often does not produce very good code but it does help a lot with syntax and how to address things like the X axis.

[RHEDE]

I hope your holiday was nice dflak.

Obviously you didn't "invent" it, however you helped me and invented the idea about how to approach this problem. Equally good in my opinion. So, thank you. Any pointers on how to approach the plotting of a variable amount of data points? I.e. if I'd want more than AA, BB, CC, DD. Like AA-ZZ.

[dflak]

To extend the range you would have to modify the definitions of X_Plot and Y_Plot

=IF(Chart!$B$3="None",Plot_None,OFFSET(Data!$A$1,1,MATCH(Chart!$B$3,Data!$A$1:$T$1,0)-1,4,1))
=OFFSET(Data!$A$1,1,MATCH(Chart!$B$2,Data!$A$1:$T$1,0)-1,4,1)

Change the 4 to match the number of rows.

You might also have to play with the definition of Plot_None =Lookups!$E$2:$E$5