Maintaining a fixed range

[cwhaley]

I have a feeling that there's a simple solution to this, but so far it
has eluded me.

I want a chart that continually shows a 52 week time series... e.g. $a
$1:$a$52, where a1 is the label. Charting that is simple.

But each week I have a macro that adds the newest value at the top. I
push the series down by doing an Insert at a2 and copy the newest
value (computed in a separate worksheet) to the now blank a2.

The chart is automatically updated, but $a$52 has now become $a$53, so
now I have 53 values charted instead of 52. Naturally, this repeats
every week.

How can I keep the range fixed at 52 weeks?

I'll probably be kicking myself when I hear the answer, but I don't
mind a little humility in favour of a solution. ;-)

....Charles

 

[Bob Greenblatt]

I have a feeling that there's a simple solution to this, but so far it
has eluded me.

I want a chart that continually shows a 52 week time series... e.g. $a
$1:$a$52, where a1 is the label. Charting that is simple.

But each week I have a macro that adds the newest value at the top. I
push the series down by doing an Insert at a2 and copy the newest
value (computed in a separate worksheet) to the now blank a2.

The chart is automatically updated, but $a$52 has now become $a$53, so
now I have 53 values charted instead of 52. Naturally, this repeats
every week.

How can I keep the range fixed at 52 weeks?

I'll probably be kicking myself when I hear the answer, but I don't
mind a little humility in favour of a solution. ;-)

...Charles

Make the series formula offset($a$1,0,0,52,1)