Charting Variable Ranges

  • Thread starter Thread starter Mr Tom
  • Start date Start date
M

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?
 
you can define a variable length range name by somethinglike th
following

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

you may need to use counta or countif instead of count depending o
your sheet
 
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?
 
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/
_______
 
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 -

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

- Jon
 
Back
Top