This is a problem with "dynamic charts" that occurs in Excel 2007 but not in
Excel 2003. Create a SS in Excel 2007 with two worksheets. On the first
sheet, enter a column of data (a few rows' worth is enough). On the other
sheet, define a single-cell named range "years". Now go back to the first
sheet, and define a named range calling it, say, "data" as
=OFFSET($B$5,0,0,years,1) (where $B$5 is the first cell of data entered, of
course). Now create a "dynamic" column chart from that data range by creating
a chart from the column of data then changing the series data to refer to the
range "data". Play with the "years" number to see that indeed you have a
dynamic chart in hand. Save the spreadsheet. Close it. Re-open and check the
data source of the chart series. It will read "=[0]!data" instead of
"=Sheet1!data".

The problem above is not an issue if the "years" range is on the same sheet
as the data. However, in my app, I have several dynamic charts of the same
dynamic size (not coincidentally, number of years is the parameter) with data
coming from several different sheets. I have an inelegant possible
work-around in mind but would really appreciate the comments and/or
suggestions from the users of this discussion board. Thanks in advance!

I've seen similar problems with charts that rely on names as their source
data. I'll play with it, and if I replicate the behavior I'll file it as a
bug.

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


"Intuitive Analyst" wrote in
message ...
This is a problem with "dynamic charts" that occurs in Excel 2007 but not
in
Excel 2003. Create a SS in Excel 2007 with two worksheets. On the first
sheet, enter a column of data (a few rows' worth is enough). On the other
sheet, define a single-cell named range "years". Now go back to the first
sheet, and define a named range calling it, say, "data" as
=OFFSET($B$5,0,0,years,1) (where $B$5 is the first cell of data entered,
of
course). Now create a "dynamic" column chart from that data range by
creating
a chart from the column of data then changing the series data to refer to
the
range "data". Play with the "years" number to see that indeed you have a
dynamic chart in hand. Save the spreadsheet. Close it. Re-open and check
the
data source of the chart series. It will read "=[0]!data" instead of
"=Sheet1!data".

The problem above is not an issue if the "years" range is on the same
sheet
as the data. However, in my app, I have several dynamic charts of the same
dynamic size (not coincidentally, number of years is the parameter) with
data
coming from several different sheets. I have an inelegant possible
work-around in mind but would really appreciate the comments and/or
suggestions from the users of this discussion board. Thanks in advance!

Thanks for the reply! Please let me know how it goes.

"Jon Peltier" wrote:

I've seen similar problems with charts that rely on names as their source
data. I'll play with it, and if I replicate the behavior I'll file it as a
bug.

- Jon

Did you have any luck reproducing this bug? Thanks in advance!

"Jon Peltier" wrote:

I've seen similar problems with charts that rely on names as their source
data. I'll play with it, and if I replicate the behavior I'll file it as a
bug.

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


"Intuitive Analyst" wrote in
message ...
This is a problem with "dynamic charts" that occurs in Excel 2007 but not
in
Excel 2003. Create a SS in Excel 2007 with two worksheets. On the first
sheet, enter a column of data (a few rows' worth is enough). On the other
sheet, define a single-cell named range "years". Now go back to the first
sheet, and define a named range calling it, say, "data" as
=OFFSET($B$5,0,0,years,1) (where $B$5 is the first cell of data entered,
of
course). Now create a "dynamic" column chart from that data range by
creating
a chart from the column of data then changing the series data to refer to
the
range "data". Play with the "years" number to see that indeed you have a
dynamic chart in hand. Save the spreadsheet. Close it. Re-open and check
the
data source of the chart series. It will read "=[0]!data" instead of
"=Sheet1!data".

The problem above is not an issue if the "years" range is on the same
sheet
as the data. However, in my app, I have several dynamic charts of the same
dynamic size (not coincidentally, number of years is the parameter) with
data
coming from several different sheets. I have an inelegant possible
work-around in mind but would really appreciate the comments and/or
suggestions from the users of this discussion board. Thanks in advance!

I've been too busy to look.

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


"Intuitive Analyst" wrote in
message ...
Did you have any luck reproducing this bug? Thanks in advance!

"Jon Peltier" wrote:

I've seen similar problems with charts that rely on names as their source
data. I'll play with it, and if I replicate the behavior I'll file it as
a
bug.

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


"Intuitive Analyst" wrote in
message ...
This is a problem with "dynamic charts" that occurs in Excel 2007 but
not
in
Excel 2003. Create a SS in Excel 2007 with two worksheets. On the
first
sheet, enter a column of data (a few rows' worth is enough). On the
other
sheet, define a single-cell named range "years". Now go back to the
first
sheet, and define a named range calling it, say, "data" as
=OFFSET($B$5,0,0,years,1) (where $B$5 is the first cell of data
entered,
of
course). Now create a "dynamic" column chart from that data range by
creating
a chart from the column of data then changing the series data to refer
to
the
range "data". Play with the "years" number to see that indeed you have
a
dynamic chart in hand. Save the spreadsheet. Close it. Re-open and
check
the
data source of the chart series. It will read "=[0]!data" instead of
"=Sheet1!data".

The problem above is not an issue if the "years" range is on the same
sheet
as the data. However, in my app, I have several dynamic charts of the
same
dynamic size (not coincidentally, number of years is the parameter)
with
data
coming from several different sheets. I have an inelegant possible
work-around in mind but would really appreciate the comments and/or
suggestions from the users of this discussion board. Thanks in
advance!

Intuitive Analyst may be in luck. It sounds like the problem he's referring
to is taken care of with Office 2007 Service Pack 1. Here's a description of
one of the problems SP1 fixes:

"The Chart Data Series value field does not retain the value set by using a
reference to a Named range on another sheet. The workbook name is changed to
"[0]." No errors are displayed. The chart simply is not updated when you add
new data."

I'm running into nearly the same problem, except that when I re-open my
workbook with dynamic charts (based on Named ranges), I immediately get the
message, "A formula in this worksheet contains one or more invalid
references". When I open the same workbook in Excel 2002, everything works
fine. I'm not sure what's different in my case, but after downloading SP1, I
still get the same error message. I've heard that the problem doesn't exist
if the named ranges are on the same worksheet as the chart, but this is
pretty limiting.

Jeff

"Jon Peltier" wrote:

I've been too busy to look.

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


"Intuitive Analyst" wrote in
message ...
Did you have any luck reproducing this bug? Thanks in advance!

"Jon Peltier" wrote:

I've seen similar problems with charts that rely on names as their source
data. I'll play with it, and if I replicate the behavior I'll file it as
a
bug.

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


"Intuitive Analyst" wrote in
message ...
This is a problem with "dynamic charts" that occurs in Excel 2007 but
not
in
Excel 2003. Create a SS in Excel 2007 with two worksheets. On the
first
sheet, enter a column of data (a few rows' worth is enough). On the
other
sheet, define a single-cell named range "years". Now go back to the
first
sheet, and define a named range calling it, say, "data" as
=OFFSET($B$5,0,0,years,1) (where $B$5 is the first cell of data
entered,
of
course). Now create a "dynamic" column chart from that data range by
creating
a chart from the column of data then changing the series data to refer
to
the
range "data". Play with the "years" number to see that indeed you have
a
dynamic chart in hand. Save the spreadsheet. Close it. Re-open and
check
the
data source of the chart series. It will read "=[0]!data" instead of
"=Sheet1!data".

The problem above is not an issue if the "years" range is on the same
sheet
as the data. However, in my app, I have several dynamic charts of the
same
dynamic size (not coincidentally, number of years is the parameter)
with
data
coming from several different sheets. I have an inelegant possible
work-around in mind but would really appreciate the comments and/or
suggestions from the users of this discussion board. Thanks in
advance!