Error re-calculating a workbook

Mattastic

New Member
Joined
Jul 25, 2011
Messages
18

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-com:office:office" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
My questions; The obvious one; What is the cause? Secondly any suggestions on something generic I can do to improve the situation?<o:p></o:p>
<o:p></o:p>
I use the MoreFunc add-in to provide the Indirect.ext function.<o:p></o:p>
<o:p></o:p>
Your thoughts and suggestions will be much appreciated. <o:p></o:p>

Rgds

Matt
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
=INDIRECT.EXT("'C:\Users\Public\Documents\WODs\["&$B$1&"]Status_Report'!"&SR_Cell_Definition!K9)

B1 is the file name of the Reference Workbook.
 
Upvote 0
Try this instead:-
="'C:\Users\Public\Documents\WODs\["&indirect($B$1)&"]Status_Report'!"&SR_Cell_Definition!K9)
 
Upvote 0
Hi Richard,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
Hey thanks for the efforts so far ;-)<o:p></o:p>
<o:p></o:p>
I think there is either a parentheses missing or more likely assume it was a cut and paste error as i cant see what it is doing otherwise.<o:p></o:p>
<o:p></o:p>
I removed the end bracket and entered it without, with the same resulting #REF, but this time it does not seem to resolve at all. And for sanity also C&P directly as written but Excel didn’t like it.<o:p></o:p>
<o:p></o:p>
If i recall, in a previous workbook this was the original issue that ended with me using INDIRECT.EXT, but this was a while back and most likely all relevant grey matter has been filed!
 
Upvote 0
Thanks for the pointer, but not sure I understand the reason for the error?

Step1
="'C:\Users\Public\Documents\WODs\["&INDIRECT("xxx_xxxx_xxxxx_xxxx.xlsx")&"]Status_Report'!"&SR_Cell_Definition!K9

Step2
="'C:\Users\Public\Documents\WODs\["&#REF!&"]Status_Report'!"&SR_Cell_Definition!K9
 
Upvote 0
It looks like the formula is struggling to get the name of the workbook to match a workbook in that location.
I know it's obvious, but have you double-checked the name and location of the workbook?
 
Upvote 0
Hi Richard, nothing at all wrong with your formula, the reason for failure is simple!!!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Lack of sleep is impacting my reasoning...... I missed something very basic here. INDIRECT only works on opened workbooks.... we intended not to open 30+ workbooks to update the consolidation file, hence the use of INDEX.EXT which takes me back to the original points.<o:p></o:p>
<o:p></o:p>
But, thanks for your help all the same ;-) and sorry for utilising your time so recklessly!

Bst rgds

Matt
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,718
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top