Using a Named Range for Data Source In A Chart

[Guest]

I am trying to create a chart that picks up the last 12 months of data
I have created a named range called Test.
Test refers to =OFFSET(Sheet1!$B$1,1,Sheet1!$C$1,2,12)
When Sheet1!C1 changes the range location changes.
I have created a column chart using Test as the Data Source, and that works
fine when I first build the Chart.
When I change the content of Sheet1!C1 from 1 to 2 (for example), I expected
the Chart to change but it does not.
I have to go back into the Data Source of the Chart and type =Test in order
for the chart to change.

My question is there any way to have the chart automatically pick up the
data that results from the named range called Test.

Any help would be appreciated.

 

[Del Cotter]

I have created a column chart using Test as the Data Source, and that works
fine when I first build the Chart.
When I change the content of Sheet1!C1 from 1 to 2 (for example), I expected
the Chart to change but it does not.
I have to go back into the Data Source of the Chart and type =Test in order
for the chart to change.

I'm not sure, but I think you could try typing =Sheet1!Test into the
Data Source of your chart. When I leave off the sheet name, my charts
often don't work at all.

 

[Jon Peltier]

When you enter a name into the data source of a chart, Excel includes the
right data, but it converts the name to the address of the named cells when
the dialog is closed. Further adjustment of the name is not reflected in the
chart.

You can use names for the X and Y values of an individual series (e.g.,
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html), which affects
the number of points in a series. You cannot use a 2D dynamic name, which
would also affect the number of series in the chart.

- Jon