Dynamic Range as Chart Data Source: Excel turns it into an absolute reference

[jamsta1972]

Hi all,

I've set up a dynamic named range called 'Res_Code_Chart_Data', using an Offset formula: "=OFFSET(Incidents!$U$43,0,0,COUNTA(Incidents!$U$43:$U$53),2)"

The range grows and shrinks dynamically as expected when I add and remove data. I want to use the range as the data source for a chart. But, when I enter a reference to the named range as the Chart Data Source "=Incidents!Res_Code_Chart_Data", Excel immediately resolves the reference to the absolute reference 'Incidents!$U$43:$U$52". After that, when data is added or removed and the named range grows or shrinks, the chart continues to only pull data from the absolute ref the named range represented when originally created.

Any advice on this issue, much appreciated. I'm using Excel 2010.

Jamsta.

[Kaper]

Have you tried to use two separate names for x avis and value axis and use it in series definition:

'Res_Code_Chart_X', using an Offset formula: "=OFFSET(Incidents!$U$43,0,0,COUNTA(Incidents!$U$43:$U$53),1)"
and
'Res_Code_Chart_Y', using an Offset formula: "=OFFSET(Incidents!$U$43,0,1,COUNTA(Incidents!$U$43:$U$53),1)"

[jamsta1972]

Hi Kaper,

Thanks for your reply. I hadn't tried that but I tried just now. Initially I got the same results, so my references to 'Res_Code_Chart_X' and 'Res_Code_Chart_Y' in the Chart Data Source were converted to absolute references.

But, I had been working with calculations set to manual. I tried again with them set to automatic, and this time is seemed to work OK, ie the references to the named ranges were retained in the Chart Data Source.

However... when I save the workbook and then reopen it, the references mysteriously revert to the absolute cell refs again. I'm completely mystified!