Error re-calculating a workbook

[Mattastic]

Hi all, after a frustrating weekend and fruitless research I come to you with a plea for assistance! <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have, what I think, is a reasonably complex workbook, plagued by a strange occurrence I have never seen before. The Workbook references a number of external workbooks (up-to 30) and returns relevant data into a number of specific worksheets.<o></o>
<o></o>
With either Options - Workbook Calculation set in auto or manual when the book recalculates more often than not, the external workbook references (Indirect.ext) return a #REF error that is only resolved after either a number of further forced recalculations or save /close /open. But neither of these exclusively resolve the issue every time.<o></o>
<o></o>
My questions; The obvious one; What is the cause? Secondly any suggestions on something generic I can do to improve the situation?<o></o>
<o></o>
I use the MoreFunc add-in to provide the Indirect.ext function.<o></o>
<o></o>
Your thoughts and suggestions will be much appreciated. <o></o>

Rgds

Matt

 

[Mattastic]

Well I am still stuck on the initial issue, but right now creating a copy using INDIRECT instead, which the users can suffer with for the time being. But this does cause an issue with losing data once the reference spread sheets are close and the file re-calculates. I can turn auto calculate off, but this is not the right solution!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Maybe I will start another thread to see if there is a solution to lock data or automatically replace the formulae with static data when the data is imported - VBA I guess, but trying to avoid that due to archaic security policy <o></o>

Thanks again.

 

[RichardMGreen]

Using VBA, you could open each workbook in turn and copy the data across.
It's not huge amounts of code and the copy/paste takes seconds.