how to automatically change source data in a chart?
Posted by Kevin on June 12, 2001 8:17 AM
I am tracking number of customers at the end of each month, and I have approximately two years worth of data compiled. I have a chart in the worksheet that shows a line graph of the no. of customers each month for one year, ending with the current month. Right now I go into the chart menu and manually change the data source, for example if B21:B33 contained the data for may-00 to may-01, at the end of June, I would change the data source for the chart to B22:B34, moving everything down one row so that my chart would show data for june-00 to june-01. Is there any way that I could make this happen automatically as the months progressed?
TIA
kevin
Posted by Mark W. on June 12, 2001 9:26 AM
Kevin, you could create a defined name for
Sheet1!X_Range as...
=OFFSET(INDEX($B:$B,MATCH(TODAY(),$B:$B)),-12,,12)
...and a defined name for Sheet1!Y_Range as...
=OFFSET(X_Range,,1)
The definition of Sheet1!Y_Range presumes that
your customer counts are in column C:C. If that's
not the case then adjust the 3rd OFFSET argument
to an appropriate value. Using these definitions
you can use =SERIES(,Sheet1!X_Range,Sheet1!Y_Range,1)
as your chart's SERIES function and the ranges
will be automatically adjusted based on your
computer's system date.
Posted by Kevin on June 12, 2001 12:59 PM
thanks!
