Dsum - external cell reference

[stan562]

I have a DSUM formula that references an external spreadsheet for th
'database' and 'field' arguments. The 'criteria' argument references
range in the same spreadsheet as the DSUM formula. The DSUM will onl
calculate if the external spreadsheet is open. Is there any wa
(without macro's) to have the DSUM successfully calculate based on th
data in an external spreadsheet without opening the externa
spreadsheet

 

[Vasant Nanavati]

AFAIK, the "D" functions do not work with closed workbooks. If leaving the
workbooks closed is important, you may want to look into array formulas or
pivot tables.

 

[Peo Sjoblom]

Adding to what Vasant said here's an example of a formula that uses a table
in the closed workbook and one criteria in the closed workbook, then a range
and one criteria in the workbook with the formula, finally it sums a range
in the closed workbook where the 2 criteria are met

=SUMPRODUCT(--('C:\Documents and Settings\My
Name\Desktop\[Testing.xls]Sheet1'!$A$4:$A$20='C:\Documents and Settings\My
Name\Desktop\[Testing.xls]Sheet1'!$F$2),--($B$4:$B$20>=$H$2),('C:\Documents
and Settings\My Name\Desktop\[Testing.xls]Sheet1'!$B$4:$B$20))


I am sure you can cover most situations that you use a DSUM for with
SUMPRODUCT although the speed will likely suffer quite a bit if the tables
are large

--

Regards,

Peo Sjoblom

AFAIK, the "D" functions do not work with closed workbooks. If leaving the
workbooks closed is important, you may want to look into array formulas or
pivot tables.