Ref# error on sum(index(match))

Ok I have a workbook with several linked workboooks. Currently to update values ALL the source files have to be opened. I figured it was the use of the OFFSET() function that caused this, so to fix this I have tried index match and sums. Still get the REF# error until I open the source file.

Old code

Please Login or Register  to view this content.

Tried a sumproduct to replace still gives ref#

Please Login or Register  to view this content.

tried just sum still gives ref#

Please Login or Register  to view this content.

Individually the Index() functions work, but when I try to sum(index():Index()) I get ref#.

Any help would be Apprecaited. Oh and when the source file is open it works just fine.

Thanks
JR

REF# suggest tat there is missing range or tab/workbook - generally formula is pointing to non existing data.
Verify all your links

Assuming that you have dates in Results'!$D$9:$D$374 and you want to sum Results'!$CM$9:$CM$374 for rows when those dates are >= MTD!H15 and <=B4 try this formula

=SUMPRODUCT((K:\Mill\Shared Files\[Operator Data 2014 MTN.xlsx]Results'!$D$9:$D$374>=MTD!H15)*(K:\Mill\Shared Files\[Operator Data 2014 MTN.xlsx]Results'!$D$9:$D$374<=B4),K:\Mill\Shared Files\[Operator Data 2014 MTN.xlsx]Results'!$CM$9:$CM$374)