This is a follow-up to a previous thread. I have found a way to reliably repro the problem! Here it is:
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 th years number to see that indeed you have a dynamic chart at hand. Save the spreadsheet. Close the file. Re-open and check the data source of the chart series. It will read "=[0]!data" instead of "=Sheet1!data".
I've attached an example. The problem above does not happen 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 th 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 of the hive mind.
Bookmarks