Macro to shift all source data in a chart by one row or column?

[Shane Henderson]

Hello all,

I have a spreadsheet which contains at least twenty years of data and twenty
or thirty charts. Each year we update all of the charts to move the forecast
period to the current year and the next five years. Because there is data
well beyond the five year period, a dynamic range for the chart data would
not work.

Can anyone suggest if it is possible to write a macro which I can run on
each chart to shift the source data for each series in the chart by one column
or row to the right?

Any suggestions would be gratefully received!

Thanks in advance,

Shan

 

[Don Guillett]

try this defined name formula where col C has properly formatted dates
=OFFSET(Sheet5!$C$1,MATCH(TODAY(),Sheet5!$C:$C),0,5,1)