Updating links...

Posted by Bill Fantin on December 07, 2001 6:44 AM

I have a workbook (WB1.xls) which draws data through a SUMIF formula from aonther workbook (WB2.xls). The links work well while both workbooks are open, but if I open WB1.xls only, the links will not update, even if I try Edit>Links>Update Now. Any suggestions on what is causing this problem or what I can do to fix it?

Thanks.

Posted by Mark W. on December 07, 2001 7:24 AM

If [WB2.xls]!A1:B6 contains...

{"Fruit","Qty"
;"Apples",100
;"Oranges",200
;"Apples",300
;"Apples",400
;"Oranges",500}

Use the array formula...

{=SUM(([WB2.xls]Sheet1!$A$2:$A$6="apples")*[WB2.xls]Sheet1!$B$2:$B$6)}

...instead of...

=SUMIF([WB2.xls]Sheet1!$A$2:$A$6,"Apples",[WB2.xls]Sheet1!$B$2:$B$6)

Note: Array formulas must be entered using
the Control+Shift+Enter key combination.
The outermost braces, {}, are not entered by
you -- they're supplied by Excel in recognition
of a properly entered array formula.

Posted by Bill Fantin on December 10, 2001 10:51 AM

Thanks for the advice. The array formula works well, but only for a specified range (i.e. $A1:B$6). I would like to open up the range to include the entire column, for example

=SUM(([WB2.xls]Sheet1!$A:$A="apples")*[WB2.xls]Sheet1!$B:$B)

But I get a #NUM error message when I try this. Any other ideas?

Thanks,
Bill.