Excel2000: Is it possible to use named ranges as chart's series ranges

A

Arvi Laanemets

Hi

I have a table, with a row added weekly, and a chart based on those data.
But chart must display data from last 6 week only, i.e. chart's data series
must be defined dynamically. Of course I can do it, using some hidden dummy
sheet, where data for last 6 week are automatically collected, and which
serves as source table for chart. But is there a way to avoid this - p.e. by
using dynamic named ranges? I tried to enter range names into Values field
for Chart Series, but I got 'The formula you typed contains an error'
message. Is there some trick for it, or it is hopeless?
 
A

Arvi Laanemets

OK, no need for help. I didn't know at start, that for dynamic chart named
ranges must be defined as SheetName.RangeName.
 
M

Mike A

Hi

I have a table, with a row added weekly, and a chart based on those data.
But chart must display data from last 6 week only, i.e. chart's data series
must be defined dynamically. Of course I can do it, using some hidden dummy
sheet, where data for last 6 week are automatically collected, and which
serves as source table for chart. But is there a way to avoid this - p.e. by
using dynamic named ranges? I tried to enter range names into Values field
for Chart Series, but I got 'The formula you typed contains an error'
message. Is there some trick for it, or it is hopeless?
Click to expand...

Type your range reference into the dialog using the = sign:

=MyRange


Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers
 
J

Jon Peltier

You need the sheet or workbook name:

=Sheet1!MyRange or =Book1.xls!MyRange

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

Mike A

You need the sheet or workbook name:

=Sheet1!MyRange or =Book1.xls!MyRange

- Jon
-------
Click to expand...

You are probably right.

The trivial example I tested before replying had the chart on the same
sheet as the data, and worked fine without the sheet name.


Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers
 
J

Jon Peltier

Mike -

I'm surprised it worked. Excel XP didn't allow a range name without a
qualifying sheet or workbook name, no matter where the named range was.

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

icestationzbra

i entered only the named range, saved and came out. then went back in
now it had automatically taken the filename concatenated with the name
range
 
J

Jon Peltier

ISZ -

If you're using 2003, then it works because Microsoft made Excel smarter
about using lists of data within the worksheets.

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