I know you can do a sumproduct on a closed workbook.
AFAIK, EditLinksOpen
source every time any changes are made.
HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
"sharon p" wrote:
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))*('\\u sflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$B$4:$B$10997=$A104)*('\\usflaph 7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$D$4:$D$10997=$B104)*('\\usflaph 7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$E$4:$E$10997))+SUMPRODUCT(--(NOT(ISERROR(SEARCH("WVR",'\\usflaph7\ADHOC_Report s\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('\\usflap h7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_ELIM_716'!$D$4:$D$2000)=MONTH(BS$1))*('\\usfla ph7\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))*('\\u sflaph7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$B$4:$B$10997=$A104)*('\\usflaph 7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$D$4:$D$10997=$B104)*('\\usflaph 7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_HFM_BALANCES'!$E$4:$E$10997))+SUMPRODUCT(--(NOT(ISERROR(SEARCH("WVR",'\\usflaph7\ADHOC_Report s\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('\\usflap h7\ADHOC_Reports\WCF\2009-08-31\[PS_TB.xls]WCF_ELIM_716'!$D$4:$D$2000)=MONTH(BS$1))*('\\usfla ph7\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