Excel 2007 dynamic chart range
 

The table below feeds a 3d Column Chart in Excel 2007.
It needs to be dynamic, so when figures for 4/6/2010, 4/7/2010, etc are
added the chart updates appropriately.

The €œ4/1/2010€� is in cell c1.

There is a dynamic range in use, where c1.cc6 is named €œdynRngSalesProg€� €¦
=OFFSET(SalesProgress!$C$1,0,0,6,COUNTA(SalesProgr ess!dynRngSalesProg)

€¦ But the chart [series] turns that into a static range.

On 4/3/2010 the charts Select Data showed the following:
='SalesProgress'!$A$1:$A$6,'SalesProgress'!$C$1:$E $6

Even though what was driving the chart originally was:
='SalesProgress'!$A$1:$A$6,OFFSET(SalesProgress!$C $1,0,0,5,COUNTA(SalesProgress!$C$1:$e$1).

I had to manually adjust the chart in Select Data for the series from $e$6
to $f$6.

Is there anything that can be done with this situation?

REGION 4/1/2010 4/2/2010 4/3/2010 4/5/2010
North 2,266 2,499
West 1,219 1,344
South 3,158 3,468
East 2,815 2,874 3,001 3,040
Canada 2,702 2,745 2,780 2,882