Chart with relative reference source

jfish1288

Board Regular
Joined
Jun 22, 2011
Messages
116
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:
Code:
=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:
Code:
=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:
Code:
=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?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jfish1288

Board Regular
Joined
Jun 22, 2011
Messages
116
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top