Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Macro Scatter Graph Plot
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Macro Scatter Graph Plot
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(X ITEM,$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(Y ITEM,$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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Macro Scatter Graph Plot
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! |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Macro Scatter Graph Plot
Yes,
Use this free addin to link data label to cell. http://www.appspro.com/Utilities/ChartLabeler.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info wrote in message oups.com... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I plot an XY (scatter) graph with two Y axes? | Charts and Charting in Excel | |||
why does sorting change a scatter plot graph? | Charts and Charting in Excel | |||
Connecting Points In An XY-Scatter Plot Graph | Charts and Charting in Excel | |||
Scatter Plot graph | Charts and Charting in Excel | |||
Scatter plot and line graph combination | Charts and Charting in Excel |