12 months rolling data in graph format

**brissy** · 06-20-2007, 06:47 AM

Hi

I wonder if anyone can help with a grapfh query. I need to be able to plot 12 months rolling data on a graph, but I need to be able to refresh the data monthly and the graph to change to the next 12 months rolling data? But I don't want to have to ask the graph to change its source data every month.

thanks
Brissy

**sweep** · 06-20-2007, 06:52 AM

Hi,

Use a named range for your graph's source data, and define the named range to dynamically select the last 12 entries.

**brissy** · 06-20-2007, 07:05 AM

that great but how do I get it to look at the last 12 months by just using a refresh button? I do not want to have to change the source data every month

thanks

**sweep** · 06-20-2007, 07:47 AM

You don't need to use the refresh button.

If you have a list of data in Column A, insert a named range that is defined like this:

=OFFSET(Sheet1!A1,COUNT(Sheet1!A:A)-12,0,12,1)

This refers to the last 12 entries in the row, so you can keep adding data month by month and the named range will always refer to the last 12 entries.

Insert your named range into the graph source data, and whenever the data is updated, the graph will update as well.

Please see attachment