 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2004Updating links that use SUMIF
I am having a problem a link to another workbook. The link uses SUMI formulas. When I go into the destination workbook, I hit update file. The formula becomes #VALUE. I go under Edit/Links (using Check Status Update Values) and it remains #VALUE. The odd thing is that when I hi check status, the source workbook changes from unknown to OK. If I g in the next time, it reverts back to Unknown. I have installed Service Pack 3 and thing have not improved. I als have Caluclation set to Update Remote References. The only way that can get the formula to work correctly is to open the source workbook. Then the formula works perfectly. Does anyone have any ideas on how to automatically do this withou having to open the source workbook? I want to ultimately have multiple source workbooks rolling into thi summary (destination) workbook. I'd rather not have to open multipl source workbooks to get it to work SUMIF (or COUNTIF) won't work with closed workbook links, you would need to replace them with SUMPRODUCT which will work, example: =SUMIF('C:\Documents and Settings\SjoblomP\Desktop\[test.xls]Sheet1'!$A$1:$A$10,"a",'C:\Documents and Settings\SjoblomP\Desktop\[test.xls]Sheet1'!$B$1:$B$10) will return an error =SUMPRODUCT(--('C:\Documents and Settings\SjoblomP\Desktop\[test.xls]Sheet1'!$A$1:$A$10="a"),'C:\Documents and Settings\SjoblomP\Desktop\[test.xls]Sheet1'!$B$1:$B$10) will work Regards, Peo Sjoblom > I am having a problem a link to another workbook. The link uses SUMIF > formulas. When I go into the destination workbook, I hit update file. [quoted text clipped - 14 lines] > summary (destination) workbook. I'd rather not have to open multiple > source workbooks to get it to work. SUMIF and COUNTIF only work with open workbooks  SignatureRegards, Tom Ogilvy > I am having a problem a link to another workbook. The link uses SUMIF > formulas. When I go into the destination workbook, I hit update file. [quoted text clipped - 14 lines] > summary (destination) workbook. I'd rather not have to open multiple > source workbooks to get it to work. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.