Formula to pull data from named ranges in multiple workbooks

[KM1234]

Hi

I am looking for some help with using named ranges across multiple workbooks, as this is the first time I have used named ranges.

I have a summary workbook which pulls data from 3 other workbooks.

In workbooks 1-3 I have defined named ranges.
In the summary workbook I have used a simple SUMIF formula, which takes data from the 3 spreadsheets and adds it together. My formula is therefore:

=SUMIF('Workbook1.xls'!ClientRange1,$B213,'Workbook1.xls'!DeferredRange1)
+SUMIF('Workbook2.xls'!ClientRange2,$B213,'Workbook2.xls'!DeferredRange2)
+SUMIF('Workbook3.xls'!ClientRange3,$B213,'Workbook3.xls'!DeferredRange3)

This works fine when Workbooks1-3 are open, but when they are closed I get #VALUE! errors. Does anyone know of a way round this, while still keeping the ranges?

I am using Excel 2003.

Thank you

 

[Andrew Poulsom]

Does this work?

=SUMPRODUCT(--('Workbook1.xls'!ClientRange1=$B213),'Workbook1.xls'!DeferredRange1)
+SUMPRODUCT(--('Workbook2.xls'!ClientRange2=$B213),'Workbook2.xls'!DeferredRange2)
+SUMPRODUCT(--('Workbook3.xls'!ClientRange3=$B213),'Workbook3.xls'!DeferredRange3)

 

[KM1234]

Works perfectly! Thank you for the swift response!