Creating a chart with different data point formats

[SDV]

Hi everyone!

You have given me incredible solutions to my previous question, and I hope you can once again help me out here!

1. I want to create a chart that looks like the attached PDF file. I used scatter charts, and added labels as text boxes for the x-axis. So far, so good.

2. I have differentiated the data points (colour, shape, outline) based on performance, functional and leadership ratings, and added + or - to show change from the previous year.

3. I changed the data points one by one to get them the way I wanted, but now I have to do it for hundreds of data points.


Q1. Is there any way to set automatic (or dynamic) chart settings where the data points will get formatted depending on the data used?
Q2. Alternatively, is it possible to use formulas to change data points?


Thank you!

SDV

 

[Hui]

It can be done
You will need 192 Helper columns for each series
that is 3 x 4 x 4 x 2 x 2

Set them up and populate them as appropriate

I will try and get to this in the next day or so, unless somebody else jumps in!

Yes it will handle hundreds of data points per each series

 

[Hui]

Here is a start

So you will have to manually format 191 more series
Then add your data
then enjoy

See attached file for the layout

 

[SDV]

Hi Hui,

Do you mean I have to manually format 192 data points? When I add data to any of the cells, many more data points are added to the chart - I don't need 192 data points on the chart, just 1 point to represent 1 value.

I found a VBA that comes close to what I'm looking for:
http://www.clear-lines.com/blog/post/Excel-ScatterPlot-with-labels-colors-and-markers.aspx
http://www.clear-lines.com/blog/post/Excel-ScatterPlot-with-labels-colors-and-markers.aspx
I am trying to edit that to make it run on my data, with more formatting options.

Thanks
SDV

 

[Hui]

You have 192 combinations of data
3 x 4 x 4 x 2 x 2 = 192

So you need a series for each combination
You have to format each series once
Each series can have as many points as you want

You could write a macro to do it for you if you want