I have a multi-workbook workspace. There are many references across books and everything works fine except for the following formula.
=+"'[Growth Factor Input.xls]Growth Factors Monthly Terminal'!$a$9:$c"&TEXT(COUNT('[Growth Factor Input.xls]Growth Factors Monthly Terminal'!C:C)+8,0)
As you can see the formula evaluates to a cell range in the workbook Growth Factor Input.xls.
The result is then used via the INDIRECT function as follows.
=VLOOKUP(B$6,INDIRECT('[Control.xls]Growth Factor Limits'!$B$6),3)
When the workspace opens, this book is included in the workspace but Excel stops here and shows an Open File dialogue box to select the correct file.
I believe the reason for this is the default location of workbooks, currently c:\My Documents.
First I could change this default location to point to the correct place, but this is a development system and the final location is not known. I don't want to have to change the default file open location for all computers that use this workspace since that is not practical.
I can understand why Excel stops here (besides the fact the M$ is stupid), but what can I do about it?