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.