Automatically Update graph based on date

Posted by Greg Vincent on April 04, 2001 10:03 AM

I was wondering how to get a graph to change its data
range on a monthly basis. It shows the outlook for the
current month, and I would like it to autmoatically
update itself based on the current date.
Thanks in advance!
Greg

Posted by Ian on April 04, 2001 11:21 AM

Not exactly what your looking for but ...

Hi Greg,
One cool excel feature I saw posted here allows you simply to hightlight your new data (ie new month) and just drag it to the graph. The graph automatically updates with the new data. Its a pretty good band-aid if you don't get a better answer.

Good luck
Ian

Posted by mseyf on April 04, 2001 1:21 PM

Greg:

give each of your monthly data ranges a name, i.e. JanRange, FebRange, MarRange, AprRange, etc.
Then on the menu bar select Insert>Name>Define and setup a 'Names in workbook:' name like CurrMon. In 'refers to' set up formula like:

=CHOOSE(MONTH(NOW()),JanRange,FebRange,MarRange,AprRange)

then set up your graph to use the range name 'CurrMon' as one of the series that is graphed.

this graph will update based on the system clock date. If you want the graph to update based on a cell value, give the cell a name and substitute that name for NOW() in the CHOOSE formula

These instructions are rather sparse, let me know if you need further clarification.

HTH

Mark

Posted by Dave Hawley on April 05, 2001 3:16 AM

Re: Not exactly what your looking for but ...

Hi Greg

I have a number samples on my Website under "Chart tips and tricks" and "Dynamic Ranges" that may suit your needs.

Also another way to do this is:

Assumming your months are in A1:A12 and your data you are plotting is in B1:B12.

Go to Insert>Name>Define
In the "Names in workbook" box type: ByMonths
In the "Refers to" box type: =Offset($B$1,0,0,MONTH(TODAY()),1)
Click "Add" then "OK"

Now set you chart range to: =ByMonths

This will now automatically drop down 1 row each Month.

Dave

OzGrid Business Applications