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

Hi,

Using John's example I transpose the data into the range A1:I2.
The following are the 2 named ranges.
Dates: =OFFSET(Sheet1!$B$1,0,0,1,COUNTA(Sheet1!$1:$1)-1)
Sales: =OFFSET(Dates,1,0)

and the chart series is
=SERIES(,Book1!Dates,Book1!Sales,1)

If you still can not get it to work post your formulas as the problem is
with the named range and not the use of it in a chart series.

Cheers
Andy

jeffsumm wrote:
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.



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Turns out the problem was that in my offset formulas I was inadvertantly
leaving out the single quotes that need to go around the sheet name.
Now it works fine. Doh.


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

I am using the offset function to automatically update my graph, but I
need the range to only keep 12 months. Can I get the Range to find the
last populated cell and count back 12 months?
Thanks


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

See Jon Peltier's example.
http://peltiertech.com/Excel/Charts/DynamicLast12.html

Cheers
Andy

Sadler wrote:
I am using the offset function to automatically update my graph, but I
need the range to only keep 12 months. Can I get the Range to find the
last populated cell and count back 12 months?
Thanks



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info