Formulas & Closed Workbooks

[Jay]

Hi,

I've recently had to replace a few SUMIF formulae with SUMPRODUCT as
they were summing ranges in other workbooks, but, more importantly -
*closed* workbooks. Consequently the SUMIFs returned #VALUE errors
until the source files were opened.

I've tried searching Help and the web to find out which functions work
OK with closed workbooks (like vlookup for example), and which don't
(eg. sumif). But I've not been very successful.

Does anyone know where I can find this information, as i think it would
be a useful reference document.

Many thanks,

Jason

 

[Harlan Grove]

Jay wrote...
....

I've tried searching Help and the web to find out which functions work
OK with closed workbooks (like vlookup for example), and which don't
(eg. sumif). But I've not been very successful.

....

Functions that require RANGE arguments specifically won't work with
external references to closed workbooks because Excel returns
references to ranges in closed workbooks as arrays not ranges.

 

[John Smith]

I've recently had to replace a few SUMIF formulae with SUMPRODUCT as
they were summing ranges in other workbooks, but, more importantly -
*closed* workbooks. Consequently the SUMIFs returned #VALUE errors
until the source files were opened.

I've tried searching Help and the web to find out which functions work
OK with closed workbooks (like vlookup for example), and which don't
(eg. sumif). But I've not been very successful.

Does anyone know where I can find this information, as i think it would
be a useful reference document.

Many thanks,

Jason