I have this workbook containing one sheet of data and 32 charts referencing that data. There's a macro in there for clearing out some data, but you can just refuse it permission to run. I believe the problem is independent of the macro.

The 2nd set of charts (Ch1Val, Ch2Val, etc) uses dynamic ranges, e.g. ranges defined by formula instead of by reference. For example the range "Ch1Prog" is defined by the formula:

=OFFSET(Data!$B$10,Data!$J$5,0,Data!$D$7,1)
This permits me to graph a data set between two values that I want, even if I change the number of data points in the set.

The problem is that I have a macro that clears the data set before I load the data from another piece of software. When I clear A9:D329 those dynamic references are invalid because the lookup functions all return #N/A. If you clear those cells and then click on Ch1Val, Excel will throw up the error, "Invalid reference . . ." No problem there; that's expected behavior.

But when I load new, valid data, that error should disappear. If you copy cells T9:W329 back into A9:D329, those references are now valid again, and everything should work. But if you click on Ch1Val, you still get the same error.

Note that in 2007 this error only re-appears on charts that have already displayed that error. If you click on Ch2Val, there will be no error. But if you were to have clicked on Ch2Val while the refs were invalid, then Ch2Val _will_ display the error the second time. In 2010 it appears regardless of whether anything was clicked after the data was cleared.

If I just save and reopen the workbook, then there are no errors, and the charts are correct. So there's nothing actually wrong with the workbook. It's just a problem with Excel getting into an invalid state.

If I were to guess, Excel somehow marks those charts as "invalid" the first time, and then doesn't properly clear the error when the refs become valid.

This did _not_ happen in Excel 2000 (which we've been using forever, but can no longer use), but it happens in both Excel 2007 and Excel 2010. In 2007 it happens very reliably. In 2010 it happens only if I run the macro that clears the named range "Measurements" and then save, then load the "new" data.

This is a crippling bug for us, because it interferes with an automated workflow, so I have your choice of a premium six pack or a dozen donuts to anyone who can find me a workaround!

Thanks!