Challenge: Return the rightmost 12 values (rolling 12 months)

[mr tom]

Hi,

Let's say there's a worksheet with the following headers:

Jan 2005 Feb 2005 .... Dec 2012

Cells below this are completed up to the current month.

I need to chart the most recent 12 months and have this move on
automatically as more months get added.

To do this, I'll have the months numbered 1 to 12 as headings, where 12 is
the most recently completed month (rightmost), 11 is the one before that and
so on, so 1 is a year ago.

Hope this makes sense.

Any way, how on earth would I go about this? Ideally the answer will be
formula driven...

Cheers,

Tom.

 

[Andy Pope]

Hi,

You can use the OFFSET function.
This will show you the technique for rows. Just swap row and column
information for your example.
http://peltiertech.com/Excel/Charts/DynamicLast12.html

Cheers
Andy

 

[mr tom]

That's genius!

Thanks!

Tom.

Hi,

You can use the OFFSET function.
This will show you the technique for rows. Just swap row and column
information for your example.
http://peltiertech.com/Excel/Charts/DynamicLast12.html

Cheers
Andy