Excel chart changes when data is ranked

[ifu06416]

Hi there,

I have a league table with 4 weekly scores and a total column which shows the total points earned.

Each player has a line graph assigned to them that shows their weekly points over the 4 weeks.

The issue i am having is that each week when new points are added, the data is sorted by the total column so that the league table is organised with the player with the most point at the top. When the data is ranked, players A's graph changes to the new player to occupy player A's old league position. I would prefer the graph to consistently show player A's performance when the data is ranked rather than which ever player happens to occupy the data range it was originally set to graph.

ie

[TABLE="width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[TD]Week 4[/TD]
[TD][/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]alan[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]joe[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]16[/TD]
[/TR]
</tbody>[/TABLE]

if alan gains 10 points in week 4 while the others gain 1 point, alan will become the new 1st place. However once the data is sorted the graph that shows johns performance will be replaced with alans data. i would prefer the graph to continue to show johns data.

Regards,

john.