#ref! error using sum index match formula on closed workbook

[selizat]

Hi All,

I have a formula that is referrencing a closed workbook and returning #ref! error for all but two of the matches. But, the values appear when the source workbook is open. I have tried updating links etc. but to no avail


Following is the formula I have been using to sum a range when it matches a certain value. Any insight would be appreciated.

=SUM('[7283 Master 2013.xlsm]7283 Master'!$H$4:INDEX('[7283 Master 2013.xlsm]7283 Master'!$H$4:$BS$4,MATCH($I$2,'[7283 Master 2013.xlsm]7283 Master'!$H$2:$BS$2,0))).

When the source book is closed, it automatically populates the entire formula with the link. This is the shortened version.

Thanks,

 

[selizat]

I found the answer! I used a sumproduct formula instead and it works even on closed workbooks!