Dynamic stacked bar chart not working

[PBcorn]

I have several stacked bar charts - fairly simple, one x, one y axis,
graphing a table with dates across the top, series labels (12) down, to which
a column is added each month. I set up a dynamic named range for the data and
a non-dynamic named range for the series labels. This works, always selecting
the last 13 months of the data. On the Source data dialog box, data range
tab, i then swapped the old fixed range references for the new dynamic range
names, hit ok, and the graph accepts them and displays correct range, so far
so good. However when i update the data with another column (month) and go
back to the chart it has converted the ranges back to fixed references. Help!

 

[BSc Chem Eng Rick]

Hi PB

How have you set up the "dynamic" range?

 

[PBcorn]

=COUNTIF(Sheetx!$5:$5,"<="&DATE(YEAR(MAX(Sheetx!$5:$5))-2,MONTH(MAX(Sheetx!$5:$5)),DAY(MAX(Sheetx!$5:$5))))

is "ColsIgnore"

the range is :

=OFFSET(sheetx!$A$5:$A$17,0,ColsIgnore,13,25)

do you know a way around the problem?

 

[BSc Chem Eng Rick]

Your thinking looks good. Try using the INDIRECT function rather than OFFSET
to specify your range for the chart. It allows you to use a text string which
is then interpreted as a range address. I will try and recreate this and see
what I can find.

If this helps please click "Yes"
<><><><><><><><><><>