Updating links that use SUMIF

[BHawley]

I am having a problem a link to another workbook. The link uses SUMI
formulas. When I go into the destination workbook, I hit update file.
The formula becomes #VALUE. I go under Edit/Links (using Check Status
Update Values) and it remains #VALUE. The odd thing is that when I hi
check status, the source workbook changes from unknown to OK. If I g
in the next time, it reverts back to Unknown.

I have installed Service Pack 3 and thing have not improved. I als
have Caluclation set to Update Remote References. The only way that
can get the formula to work correctly is to open the source workbook.
Then the formula works perfectly.

Does anyone have any ideas on how to automatically do this withou
having to open the source workbook?

I want to ultimately have multiple source workbooks rolling into thi
summary (destination) workbook. I'd rather not have to open multipl
source workbooks to get it to work

 

[Peo Sjoblom]

SUMIF (or COUNTIF) won't work with closed workbook links, you would need to
replace them with SUMPRODUCT which will work, example:

=SUMIF('C:\Documents and
Settings\SjoblomP\Desktop\[test.xls]Sheet1'!$A$1:$A$10,"a",'C:\Documents and
Settings\SjoblomP\Desktop\[test.xls]Sheet1'!$B$1:$B$10)

will return an error

=SUMPRODUCT(--('C:\Documents and
Settings\SjoblomP\Desktop\[test.xls]Sheet1'!$A$1:$A$10="a"),'C:\Documents
and Settings\SjoblomP\Desktop\[test.xls]Sheet1'!$B$1:$B$10)

will work

Regards,

Peo Sjoblom

 

[Tom Ogilvy]

SUMIF and COUNTIF only work with open workbooks