Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > order of opening files with sumproduct returning #REF!
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2
Default order of opening files with sumproduct returning #REF!
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




Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,836
Default order of opening files with sumproduct returning #REF!
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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2
Default order of opening files with sumproduct returning #REF!
I found the issue & resolved by editing the formulas resulting in the #REF!
as an array formulas (hitting the CTRL-SHIFT-ENTER combined).


"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




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
show most recent files first when opening excel files Anne` Excel Discussion (Misc queries) 5 January 23rd 08 01:54 AM
Opening Quattro Pro for Windows files (*.WB1 Files) using Excel 20 PoundMutt Excel Discussion (Misc queries) 1 June 20th 07 03:50 AM
How can I view files chronologically when opening multiple files Stevilsize Excel Discussion (Misc queries) 3 July 26th 05 12:49 AM
sumproduct returning #NA leolin Excel Discussion (Misc queries) 6 April 28th 05 03:02 AM
Sumproduct returning #NUM! wal50 Excel Worksheet Functions 4 November 21st 04 04:01 PM


All times are GMT +1. The time now is 10:08 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement