asmithbcat said:
I have a very simple chart that I am putting together on a weekly basis. I
want the chart to show only the last four entries, but I do not want to
update the source data every time I need a new chart. However, I want a
historical record of the data, so I don't want to delete any entries.
Is there a way to set up the source data so that it only pulls the last four
entries in a specific range?
There are at least a couple ways.
One is to use "dynamic ranges" to control where the chart gets its data.
Debra Dalgleish has a very good tutorial here:
http://www.contextures.com/xlNames02.html
I personally prefer to do it another way, as dynamic ranges can be a bit
tedious at times.
Instead of plotting the chart against the "master data" sheet, create a
new area that extracts the desired values and plot the chart from that.
This works well when the master data has a unique key, such as a time
series. It also maintains a separation of the master data and the
presentation data.
Using this, the chart series range never changes, only the values within
it. For example, suppose you have a master data layout like this on a
worksheet called "Master":
Month Value
Jan 2008 39
Feb 2008 52
....
Mar 2009 55
Apr 2009 60
May 2009 44
Jun 2009 50
On a new worksheet place value "Jun 2009" in cell F1 (somewhere out of
the way). This represents the most recent data point. Name this cell
"Current".
Now build your chart data:
Offset Month Value
-3 =INDEX(Master!A$1:A$99,MATCH(Current,Master!$A$1:$A$99,0)+$A2,1)
-2
-1
0
The "month" formula above can be filled right and down to capture the
months and values you want. Now you have a static range for your chart.
When a new month is added simply update the "Current" cell... you can
probably see now there is an easy way to make "Current" update itself.
Note my references to "A$99" are intended to an arbitrarily large row to
fully contain the master data.
