Mal Osborne wrote:
Any pointers on how to troubleshoot this problem would be greatly
appreciated!


Some formulas are a bit too much of a challenge for Excel to do when
the source workbook is closed. The INDIRECT worksheet function will
never work; some other formulas seem to depend on the Excel version.
I think it was the original release of Excel 2000 that was particularly
bad. Worth ensuring you have the latest service release just in case.

Otherwise the answer is to have the other files open.
You could produce a macro to open them.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup