Linking to external workbook

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)
 
F

Frank Kabel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Linking to external files 1
variable substitution in a formula linking to external workbook 2
refer to different workbook based on value in current workbook 2
Linking a text box to an external Excel workbook 1
Follow up question for Tom Ogilvy - re: linking a text box to an external workbook 2
Dynamic link to workbook 3
Strange issues with linking 3
User Defined Function being called by changes to an independent workbook 13

Top