Scatter Plot Graph with Dynamic formatting

[shanghaileon]

Hi Guys, I'm working on creating a scatter plot with lines graph to show both progression in levels for a person, year by year, as well as the performance rating that the person received that year. I'd like to do the formatting based on rating on a yearly basis (only one rating a year) and in the year they're promoted, they still hold that same rating, but move up a level.

I have most of the ground work laid for what I"m trying to do in the attached file, but I"m having trouble getting rid of connector lines across non sequential data points. For example, in 2013, someone is green, 2012, someone is yellow, and 2011 they were also green. I want each of those years colored respectively, but not 2011 with a line to 2013.

Any help would greatly be appreciated. I know that I can get around some of these issues by ordering the series, and I have a backup version that doesn't use the connecting lines which is decent, but I'm hoping there's a way to make this work! Thanks in advance!

Each vertical gridline represents a year.

 

[Worf]

The following is a scatter plot with separate series for each line segment:

 

[shanghaileon]

I think that's very similar to what I am looking for, the one difference is that I have changes happening between periods but I think I can handle that in my graphing. How did you get it to not draw the lines, can you share your file or approach please?

 

[Worf]

Here is the file, tell me if you still have doubts after inspecting it:

https://dl.dropboxusercontent.com/u/52116836/shanghai.xlsx

 

[shanghaileon]

Worf, Thanks for the quick reply. Do you know a way to do it so that the coloring of the segments is dynamic? I've split my data into separate series for the separate formatting, but am having trouble preventing the connecting of the different lines. I've put a screenshot below to give you an idea of my data set and graph in it's current state:

 

[Worf]

I could write a macro to create the chart based on the data table. Are the colors defined by the performance rating? When the performance is #N/A on an entire row, what should the color be?
I need all the details in order to write the code.