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!