Dynamic stacked bar chart not working

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!

Hi PB

How have you set up the "dynamic" range?

"PBcorn" wrote:

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!

=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" wrote:

Hi PB

How have you set up the "dynamic" range?

"PBcorn" wrote:

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!

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"


"PBcorn" wrote:

=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" wrote:

Hi PB

How have you set up the "dynamic" range?

"PBcorn" wrote:

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!