Updating a chart automatically

[jeffsumm]

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]

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

[jeffsumm]

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.

[Sadler]

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]

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