Chart with relative reference source

[jfish1288]

I am not sure if this is possible, but here is what I am trying to do.

I have several charts, each on its own row. The X-Axis for all these charts is on Row 2. The series data for each chart is on the same row as the chart.

So the formula for the Chart#1 (on row 3) this is the souce data formumla:

=SERIES('All Models'!$M$3,'All Models'!$N$2:$AE$2,'All Models'!$N$3:$AE$3,1)

The problem I am having is if I sort my table by some value and Chart#1 moves to row 10 the source data formula remains:

=SERIES('All Models'!$M$[COLOR=#ff0000][B]3[/B][/COLOR],'All Models'!$N$2:$AE$2,'All Models'!$N$[COLOR=#ff0000][B]3[/B][/COLOR]:$AE$[COLOR=#ff0000][B]3[/B][/COLOR],1)

I would like for Chart#1 to always have the series data to be in the row the chart is on. In effect I want the formula for Chart#1 to be relative not absolute. However if I change the formula (for Chart#1 when in row 3)to:

=SERIES('All Models'![COLOR=#0000ff]$M3[/COLOR],'All Models'!$N$2:$AE$2,'All Models'![COLOR=#0000ff]$N3[/COLOR]:[COLOR=#0000ff]$AE3[/COLOR],1)

it automatically reverts to being all absolute.

Is it possible to make a chart reference relative, or is there another way to acomplish what I am trying to do?

 

[jfish1288]

Im still looking for a solution. Another approach I have given some thought to but have not figure out how to do would be to have Chart #1 remain in Row3, and have the source data stay on Row 3. Is this possible?