#VALUE issue with linked files 1

[HairyHippy]

Hi

I have a .xls (file1.xls) that is lined to another .xls (file2.xls). The formula used in the vast majority of cases in file1.xls is:

=SUMIF('[file2.xls]Sheet1'!$A:$A,$A$2,OFFSET('[file1.xls]Sheet1''!$C:$C,0,$B$2))

$A$2 in the above forumla is another formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

and so is $B$2:

='[file2.xls]Sheet2!$B$1

OK, so when I open file1.xls without having file2.xls open, and the option selected to prompt user to update links or not, before selecting to update or not, I can see the numbers but as soon as I select whether to update or not (either returns the same result), all cells with formulas, bar $A$2 and $B$2, return the #VALUE error. If I then open file2.xls, the numbers return.

Regardless of if I allow the user to update links or not (start prompt), the same thing happens.

For business reasons, file 2 can not be incorporated into file 1 and I have to send file 1 out to relevant parties, therefore I need to find a solution other than having to copy and paste values and formats of file 1 into a new book.

Has anyone seen this before and/or know of a solution?

Thanks in advance!

 

[macropod]

Hi AdeyB,

The behaviour you describe is common to a number of Excel formula when linking to external workbooks - some formulae only return the correct results if the source workbook is open. Depending on what you're trying to achieve, you could:
. Turn off auto-recalculation (a pain if you actually need to do anything useful)
. use an intermediate worksheet in your target workbook that links directly to the source cells, and point your existing formulae to that instead.

Cheers

[MS MVP - Word]

 

[HairyHippy]

Thanks macropod!

Just going to have to remember when I'm updating file1.xls every month to have file2.xls open and hit F9! Not too much of a pain.