Charting Variable Ranges

[Mr Tom]

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

As time passes, more months end up on the base. I had
hoped that by using a variable range name, I'd be able to
get the charts to automatically update, e.g. define a
range for the series data as:
=INDIRECT("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?

 

[duane]

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

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

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

 

[Don Guillett]

try defining a name such as myrng
insert>name>define
=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 Guillett
SalesAid Software
(e-mail address removed)
I have an xls which contains money saved tracked against
month and year it takes place.
This is then charted.
i.e:
Jan 04 £10,000,000
Feb 04 £120,000

As time passes, more months end up on the base. I had
hoped that by using a variable range name, I'd be able to
get the charts to automatically update, e.g. define a
range for the series data as:
=INDIRECT("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?

 

[Tushar Mehta]

See
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

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]

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.

 

[Jon Peltier]

Don -

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

- Jon