I am using Excel 2002, and I need to modify a chart so that it updates
automatically. The way the spreadsheet is laid out is that it uses a
row of months at the top, a bunch of detail rows below that, and two
different total rows at the bottom. I'm only concerned with the months
row and the two total rows. In the chart (an embedded line chart on a
separate sheet from the data), the months are the x-values and the
total rows are the two data series. The amount of information being
represented is getting past two years' worth, and as time goes on, more
and more data will be squished into the same chart. The chart needs to
be modified so that it always only represents the last 24 months, and
does it automatically.

I've found a great idea for this at
http://www.j-walk.com/ss/excel/usertips/tip053.htm , which shows how to
use range names and the offset formula, and then refer to the range name
in the series formulas in the chart. The example on the above web page
has the chart representing an ever-growing range, but I wrote offset
formulas to modify the example so the chart only represents the last 5
items, or 10, or whatever, and it works beautifully.

The example, however, has columns of data, whereas the spreadsheet I'm
working with has rows of data. For some reason, the same concept won't
translate into rows. The problem comes down to this: in the series
formulas of the two data series, I can refer to the range name in the
X-values argument (click on data series in the chart and edit in the
formula bar), but Excel won't let me do it in the Values argument. In
other words, I can refer to a constantly changing range in X-values
(the months), but I can only use an absolute reference for the data
values, which kind of shoots holes in the graph being automatically
updated. When I put the range name in the data values argument, Excel
gives me an error that says "Your formula contains an invalid external
reference to a worksheet. Verify that the path, workbook and range name
or cell reference are correct, and try again."

At the j-walk website, there is also a class module that might provide
a VBA solution to this problem
(http://j-walk.com/ss/excel/tips/tip83.htm), but I'm not sure I'm in
the mood to do that, although I could if I had to.


--
jeffsumm
------------------------------------------------------------------------
jeffsumm's Profile: http://www.excelforum.com/member.php...o&userid=29853
View this thread: http://www.excelforum.com/showthread...hreadid=495532