Sliding Scale for X-Axis (Always Past 4 weeks) in Excel 2007

I have a Management Dashboard that I want to issue and update on a weekly basis.

I would like the charts on the first page/worksheet to always show the Past 4 weeks on the X-axis.

I have a separate "Weekly" data worksheet where I expect to add a column each week on the right side of the data table with the new data.

Is there a way to have the charts on the first worksheet to keep their X-Axis to be the "Past 4 weeks" without having to update the data range of the chart every week?

Have a great day.

Thanks,
Scott

Welcome to the Forum!

I have seen a couple of examples of fancy usage of named formulas to do things like this with charts, though I haven't done it myself and can't seem to locate an example.

My first thought would be to set up another sheet with four columns, and use formulas to refer to the last four columns of the primary source. Then the chart would always just refer to this additional sheet. For example, the following formulas return the last four numeric value in row 1:


=INDEX(1:1,1,MATCH(9.99999999999999E+307,1:1)-3)
=INDEX(1:1,1,MATCH(9.99999999999999E+307,1:1)-2)
=INDEX(1:1,1,MATCH(9.99999999999999E+307,1:1)-1)
=INDEX(1:1,1,MATCH(9.99999999999999E+307,1:1))