Macro Scatter Graph Plot

[simon.stewart]

Hi,
I am trying to create a dynamic scatter graph in Excel. I have a list
of 40 names each with roughly 30 columns of data corresponding to each
name. What I would like to do is create a multi list box so I could
select about 9 or 10 names. Once these are selected, I would like to
select which columns of data I would like the graph to show (perhaps
by using tick boxes?), obviously this would be limited to 2 columns of
data for the x and y axes.

So a simple example would be to select the name "Glencore" and the Col
E (value 29) and Col J (Value 128) and to click a macro button to plot
the name Glencore which has an x axis value of 29 and y axis of 128.
I have limited knowledge of vba and am better at manipulating them and
reading them rather than creating them from scratch.
Can anybody help me on how to get started? Thank you,

Simon

 

[Andy Pope]

Hi,

No need for any vba this can be done with formula.
Assumptions on data ranges for the example.

table of raw data is in A1:K27
first row contains column headers
first column contains unique name list

a named range, XITEM, contains column header text for x data
a named range, YITEM, contains column header text for y data

A33 contains name to be plotted
B33 contains formula to get x data value for person mentioned in A33
=INDEX($B$2:$K$27,MATCH($A33,$A$2:$A$27,0),MATCH(XITEM,$B$1:$K$1,0))

C33 contains formula to get y data value for person mentioned in A33
=INDEX($B$2:$K$27,MATCH($A33,$A$2:$A$27,0),MATCH(YITEM,$B$1:$K$1,0))

repeat for as many rows as you want names in chart.

Create xy scatter on range B33:Cxx where xx is the last row of charting
data.

The choice of xitem, yitem and name can be down using validation lists.

Cheers
Andy

 

[simon.stewart]

Thanks, this looks very promising and looks like I could tailor it
pretty easily.
Is it easy for these points to be labelled on the graph itself?
e.g, So the scatter point says "Glencore" next to it?
Thanks again!

 

[Andy Pope]

Yes,

Use this free addin to link data label to cell.
http://www.appspro.com/Utilities/ChartLabeler.htm

Cheers
Andy