Chart Automation

[SteveG]

Not sure if this is possible but it would certainly save me a lot of
time each month. I have a presentation that has about 50 charts. The
source data (which is in a different workbook) for the charts runs
like:

Column 1/Columns 2 through whatever.
Month /Data

The first row is all headers that act as the Chart Title and should
remain constant.

Here's my dilemna. My months range from January 2005 - June 2006.
Each new month, the cells are populated with the respective data. Not
a problem. My presentation, however, should only show the last 3
months worth of data i.e. in early December I report on Sept/Oct/Nov.

What I am currently doing is going in manually at months end and
changing the data series from last months (Aug/Sept/Oct) to the new
series (Sept/Oct/Nov). With 50 charts, this is very time consuming.
What I am looking for is a way to automate this process. Would I be
able to use a control or something along those lines? I assume that VB
would be involved and of course, that is not my forte.

Any help is much appreciated.

Thanks,

Steve

 

[Andy Pope]

Hi,

You need a dynamic chart based on a named range. No VBA required.
Jon Peltier has instructions for one that plots the last 12 periods but
you can easily modify this to only do 3 months.
http://peltiertech.com/Excel/Charts/DynamicLast12.html

Cheers
Andy

 

[SteveG]

Thanks for the info Andy. I made a quick go of it but unfortunatel
could not get it to work correctly. Not enough time to troubleshoot a
the moment but I am sure I can figure it out.

Thanks again.

Stev