Charting Variable Ranges

I have an xls which contains money saved tracked against=20
month and year it takes place.
This is then charted.
i.e:
Jan 04 =A310,000,000
Feb 04 =A3120,000

As time passes, more months end up on the base. I had=20
hoped that by using a variable range name, I'd be able to=20
get the charts to automatically update, e.g. define a=20
range for the series data as:
=3DINDIRECT("Sheet1!B1:B"&COUNTA($B:$B))
(call it varSavedByDate)
and then have the chart refer to the range by name.

Unfortunately this doesn't seem to work.

Any bright ideas?

you can define a variable length range name by somethinglike the
following

range "range" = a1ffset(count(a1:a1000),0,0,0)

you may need to use counta or countif instead of count depending on
your sheet.


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=268232

Don -

I don't know about 2003, but most versions of Excel need you to include the sheet or
workbook name when designating a range name as series data:

=Sheet1!MyRng
='Sheet Name'!MyRng
=Book1.xls!MyRng

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Don Guillett wrote:

try defining a name such as myrng
insertnamedefine
=myrng
=offset($a$1,0,0,counta($A:$A),3)
for a1:c count of all in col A.

now your series is
=myrng and excel will autofill in the workbook.xls and sheetname!

Jon, (xl2002)
I thought I had discovered this shortcut, working on a clients wb, when
changing a set range series to a named range series. However, I just tested
with a new workbook and chart and found that it did NOT work. I stand
corrected.

--
Don Guillett
SalesAid Software

"Jon Peltier" wrote in message
...
Don -

I don't know about 2003, but most versions of Excel need you to include
the sheet or
workbook name when designating a range name as series data:

=Sheet1!MyRng
='Sheet Name'!MyRng
=Book1.xls!MyRng

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Don Guillett wrote:

try defining a name such as myrng
insertnamedefine
=myrng
=offset($a$1,0,0,counta($A:$A),3)
for a1:c count of all in col A.

now your series is
=myrng and excel will autofill in the workbook.xls and sheetname!

Don -

That's the number 1 reason people have problems charting with defined names.

- Jon

Don Guillett wrote:

Jon, (xl2002)
I thought I had discovered this shortcut, working on a clients wb, when
changing a set range series to a named range series. However, I just tested
with a new workbook and chart and found that it did NOT work. I stand
corrected.