J
jeffsumm
I am using Excel 2002, and I need to modify a chart so that it update
automatically. The way the spreadsheet is laid out is that it uses
row of months at the top, a bunch of detail rows below that, and tw
different total rows at the bottom. I'm only concerned with the month
row and the two total rows. In the chart (an embedded line chart on
separate sheet from the data), the months are the x-values and th
total rows are the two data series. The amount of information bein
represented is getting past two years' worth, and as time goes on, mor
and more data will be squished into the same chart. The chart needs t
be modified so that it always only represents the last 24 months, an
does it automatically.
I've found a great idea for this a
http://www.j-walk.com/ss/excel/usertips/tip053.htm , which shows how t
use range names and the offset formula, and then refer to the range nam
in the series formulas in the chart. The example on the above web pag
has the chart representing an ever-growing range, but I wrote offse
formulas to modify the example so the chart only represents the last
items, or 10, or whatever, and it works beautifully.
The example, however, has columns of data, whereas the spreadsheet I'
working with has rows of data. For some reason, the same concept won'
translate into rows. The problem comes down to this: in the serie
formulas of the two data series, I can refer to the range name in th
X-values argument (click on data series in the chart and edit in th
formula bar), but Excel won't let me do it in the Values argument. I
other words, I can refer to a constantly changing range in X-value
(the months), but I can only use an absolute reference for the dat
values, which kind of shoots holes in the graph being automaticall
updated. When I put the range name in the data values argument, Exce
gives me an error that says "Your formula contains an invalid externa
reference to a worksheet. Verify that the path, workbook and range nam
or cell reference are correct, and try again."
At the j-walk website, there is also a class module that might provid
a VBA solution to this proble
(http://j-walk.com/ss/excel/tips/tip83.htm), but I'm not sure I'm i
the mood to do that, although I could if I had to
automatically. The way the spreadsheet is laid out is that it uses
row of months at the top, a bunch of detail rows below that, and tw
different total rows at the bottom. I'm only concerned with the month
row and the two total rows. In the chart (an embedded line chart on
separate sheet from the data), the months are the x-values and th
total rows are the two data series. The amount of information bein
represented is getting past two years' worth, and as time goes on, mor
and more data will be squished into the same chart. The chart needs t
be modified so that it always only represents the last 24 months, an
does it automatically.
I've found a great idea for this a
http://www.j-walk.com/ss/excel/usertips/tip053.htm , which shows how t
use range names and the offset formula, and then refer to the range nam
in the series formulas in the chart. The example on the above web pag
has the chart representing an ever-growing range, but I wrote offse
formulas to modify the example so the chart only represents the last
items, or 10, or whatever, and it works beautifully.
The example, however, has columns of data, whereas the spreadsheet I'
working with has rows of data. For some reason, the same concept won'
translate into rows. The problem comes down to this: in the serie
formulas of the two data series, I can refer to the range name in th
X-values argument (click on data series in the chart and edit in th
formula bar), but Excel won't let me do it in the Values argument. I
other words, I can refer to a constantly changing range in X-value
(the months), but I can only use an absolute reference for the dat
values, which kind of shoots holes in the graph being automaticall
updated. When I put the range name in the data values argument, Exce
gives me an error that says "Your formula contains an invalid externa
reference to a worksheet. Verify that the path, workbook and range nam
or cell reference are correct, and try again."
At the j-walk website, there is also a class module that might provid
a VBA solution to this proble
(http://j-walk.com/ss/excel/tips/tip83.htm), but I'm not sure I'm i
the mood to do that, although I could if I had to