Linking to external workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using SUMIF linking to an external excel workbook. If I have the external
workbook open and calculate (F9) the new file it works fine. But if I close
down the external workbook (only having the new file open) and recalculates I
get #VALUE! back. Is there a way around this so I can use the "new" file and
recalculate it without opening the external file?

Example;
=+SUMIF('G:\Access and Function Plans 2005-2009\[Financial Tool_2005_BT
Version 1.0.xls]Other Functional Activities'!$H:$H,$A14,'G:\Access and
Function Plans 2005-2009\[Financial Tool_2005_BT Version 1.0.xls]Other
Functional Activities'!K:K)
 
Hi
SUMIF does not work on closed files. But you can replace it with
SUMPRODUCT. Try:
=SUMPRODUCT(--('G:\Access and Function Plans 2005-2009\[Financial
Tool_2005_BT
Version 1.0.xls]Other Functional
Activities'!$H1:$H1000=$A14),'G:\Access and
Function Plans 2005-2009\[Financial Tool_2005_BT Version 1.0.xls]Other
Functional Activities'!K1:K1000)

Note: Sumproduct does not accept ranges like K:K -> change it to
something like K1:K1000
 
Back
Top