Auto Updating Graphs

[Jod84]

I'm trying to work out how to automatically update graphs I've read some articles on how to do this using named ranges and offset/countif but what I want to do is a little more complex.

Each monthed I update a dozen plus rolling graphs, some are past 10 years and some are past 2 years on a variety of information from currency info to electricity and diesel prices. All spreadsheets the graphs are based off have the exact same format, in column A are the dates in d/m/yy format with one record per day for as long as data is available some are 20+ years (records per month vary as some include weekends and some don't) and in column B is the data.

Any help with this would be appreciated, I was hoping to avoid using macro's as maintainability is important and I know very little VB.

 

[Steve=True]

Based on your description you are on the right track with Offset.

See this tutorial on what you are trying to do.

http://peltiertech.com/Excel/Charts/DynamicLast12.html

Since your range is not just a rolling x months, we will need to add a match function to find the closest date to 2 years or 10 years, etc.

You can use this type of formula to find the date 2 years back
=DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY()))

and then you can find the closest to it for your offset formula.
=MATCH(E1,A3:A463,-1)

Then use the offset formula described in the link to get the chart/graph plot series.

Might be good if you are able to post a sample of the data for help with the look up formula.

Hope this helps!

Steve=True
www.exceldashboardtemplates.com

 

[Steve=True]

by the way, the date formula was in E1

I then put the Match formula in E2

your offset formula may look like this:

=OFFSET(A3,E2-1,,COUNT(A:A)-E2,1)

But would need to be adjusted for where you actual data starts.

Steve=True
www.exceldashboardtemplates.com

 

[Jod84]

Based on your description you are on the right track with Offset.

See this tutorial on what you are trying to do.

http://peltiertech.com/Excel/Charts/DynamicLast12.html

Since your range is not just a rolling x months, we will need to add a match function to find the closest date to 2 years or 10 years, etc.

You can use this type of formula to find the date 2 years back
=DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY()))

and then you can find the closest to it for your offset formula.
=MATCH(E1,A3:A463,-1)

Then use the offset formula described in the link to get the chart/graph plot series.

Might be good if you are able to post a sample of the data for help with the look up formula.

Hope this helps!

Steve=True
www.exceldashboardtemplates.com

Thanks for this. I'm a bit of a noob how do you attach parts of an excel file in this thread. I'll try what your suggesting although as I quiet often to the report several days after month end I'll proably have to have the month end date in cell and refer to it rather then using TODAY formula