order of opening files with sumproduct returning #REF!

[sharon p]

Hello - this seems very bizarre...sure hope you can help

We have 2 workbooks (9277KB ea.) which reside on the same server & share
similar formulas that sumproduct to a source on another server (file size
515KB). Once we open the FF.xlsm , and subsequently open TW.xlsm, the below
formula in FF.xls returns #REF!


=SUMPRODUCT(('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$I$4:$I$10997=MONTH(BS$1))*('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$B$4:$B$10997=$A104)*('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$D$4:$D$10997=$B104)*('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$E$4:$E$10997))+SUMPRODUCT(--(NOT(ISERROR(SEARCH("WVR",'\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_ELIM_716'!$I$4:$I$2000)))),--(NOT(ISERROR(SEARCH("2007
1",'\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_ELIM_716'!$I$4:$I$2000)))),('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_ELIM_716'!$F$4:$F$2000=$B104)*(MONTH('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_ELIM_716'!$D$4:$D$2000)=MONTH(BS$1))*('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_ELIM_716'!$H$4:$H$2000))

Of course once the PS_TB source is opened the #REF! clears to a value. But
no #REF! occurs if TW.xlsm file is opened first & thereafter the FF.xlsm.
The following is the formula in the TW.xlsm file;


=SUMPRODUCT(('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$I$4:$I$10997=MONTH(BS$1))*('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$B$4:$B$10997=$A104)*('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$D$4:$D$10997=$B104)*('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$E$4:$E$10997))+SUMPRODUCT(--(NOT(ISERROR(SEARCH("WVR",'\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_ELIM_716'!$I$4:$I$2000)))),--(NOT(ISERROR(SEARCH("2007
1",'\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_ELIM_716'!$I$4:$I$2000)))),('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_ELIM_716'!$F$4:$F$2000=$B104)*(MONTH('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_ELIM_716'!$D$4:$D$2000)=MONTH(BS$1))*('\\usflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_ELIM_716'!$H$4:$H$2000))

Any idea what's going on? ....suggestions?

thanks,
Sharon

 

[ryguy7272]

I know you can do a sumproduct on a closed workbook. AFAIK, Edit>Links>Open
source every time any changes are made.

HTH,
Ryan---

 

[sharon p]

I found the issue & resolved by editing the formulas resulting in the #REF!
as an array formulas (hitting the CTRL-SHIFT-ENTER combined).