How to update destination file with source files closed?

A

Alex Costache

Hello,

I am working on a project now where I have to import data from 5 different
files. Everything works fine, as long as I have all source files opened when
I open and update the data in the destionation file. If 1 or more source
files are closed (not opened on the computer) all cells that refer to the
source files display "#VALUE". The only formulas I used are COUNTA and
COUNTIF.

On a different project I used VLOOKUP and it works perfectly, it updates the
data from the source files without any of the source files to be opened.

Please let me know what should I do (if there is something to be done), in
order to be able to use this destination file, without having to open
previously 5 other files.

Thank you.
 
T

Tom Hutchins

I am not sure about COUNT and COUNTA, but many functions (such as COUNTIF)
cannot be used on a closed workbook. You can probably do everything with
SUMPRODUCT you can do with COUNT or COUNTA, and it does work with closed
workbooks. For example, the two following formulas give the same results:

=COUNTA(C7:C18)
=SUMPRODUCT(--(C7:C18<>""))

If you are unfamiliar with SUMPRODUCT, check out this link:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps,

Hutch
 
Top