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

=SUM(OFFSET('K:\Mill\Shared Files\[Operator Data 2014 MTN.xlsx]Results'!$CM$10,MTD!H15-YTD!H15,0,DAY(B4),1))

Tried a sumproduct to replace still gives ref#

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

tried just sum still gives ref#

=SUM(INDEX('K:\Mill\Shared Files\[Operator Data 2014 MTN.xlsx]Results'!$CM$9:$CM$374,MATCH(MTD!H15,'K:\Mill\Shared Files\[Operator Data 2014 MTN.xlsx]Results'!$D$9:$D$374,0),0):INDEX('K:\Mill\Shared Files\[Operator Data 2014 MTN.xlsx]Results'!$CM$9:$CM$374,MATCH(B4,'K:\Mill\Shared Files\[Operator Data 2014 MTN.xlsx]Results'!$D$9:$D$374,0),0))

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