[RESOLVED] Excel 2007 shows #REF! errors when workbook is opened

[aaronsher]

I've got a fairly complex multi-sheet workbook. If I enter data into it, everything is fine, until I close and reopen it. At that point, various calculated values show #REF! until I modify a value; at that point, all the errors go away. Manually recalculating (F9) doesn't make the errors disappear.

Here's what's even weirder. If I choose one of the #REF! cells and use the Evaluate Formula function so that I can single-step it, I can walk through the entire calculation with no errors, until I get to the very last step. The formula eventually evaluates down to (2*1), and when I step that one, I get #REF!. What the hell?

Anybody have any idea what's going on?

Thanks,
Aaron

[aaronsher]

One more piece of data that I just discovered: if I save the workbook with the offending sheet selected, when I reopen it there's no problem. The problem only occurs if I open the workbook on a different sheet, and then switch to the one that has the errors.

[aaronsher]

At http://www.excelforum.com/excel-gene...ml#post2455899, the user "shg" posted what appears to be a solution to the problem. The issue is that the function is using Cells unqualified, which means that it's going to refer to the currently-active worksheet when the function is evaluated. Apparently, when the workbook is opened, this function gets evaluated, and if we're not looking at the right sheet, the function does bad things.

The immediate solution is to change the reference to Cells to be instead Sheets("Tasks").Cells. I'm also looking at doing some other tweaks to improve efficiency, at shg's suggestion.

Aaron