Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XL2000 - Linking SUMIF with Other Workbook
I have two workbooks. One contains a detailed sheet and the other contains a
summary sheet. On the summary sheet I want to use SUMIF so that it goes to the detail sheet and, if a specfiic condition exists in one column, it adds the dollars in an other column, and brings the result back to the summary sheet. When I set it up, both workbooks were open. When I tested it, I only opened the summary sheet. All my otherlinks actively updated except those using SUMIF. The SUMIF cells display #VALUE!, until I open the detail sheet. Is this the way SUMIF is meant to work between workbooks or is this a "bug" with SUMIF in XL2000 (O/S = XP)? All help is greatly appreciated. -- LPS |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XL2000 - Linking SUMIF with Other Workbook
There are functions that don't work when the "sending" workbook is closed.
=sumif() and =indirect() are a couple. But there are workarounds... You could use =sumproduct() Instead of using a formula like: =SUMIF('C:\My Documents\Excel\[book2.xls]Sheet1'!$A$1:$A$36,"asdf", 'C:\My Documents\Excel\[book2.xls]Sheet1'!$B$1:$B$36) You could use: =SUMPRODUCT(--('C:\My Documents\Excel\[book2.xls]Sheet1'!$A$1:$A$36="asdf"), 'C:\My Documents\Excel\[book2.xls]Sheet1'!$B$1:$B$36) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ps. I would build the formula when the sending workbook is open. Let excel worry about the syntax when the workbook is closed. LPS wrote: I have two workbooks. One contains a detailed sheet and the other contains a summary sheet. On the summary sheet I want to use SUMIF so that it goes to the detail sheet and, if a specfiic condition exists in one column, it adds the dollars in an other column, and brings the result back to the summary sheet. When I set it up, both workbooks were open. When I tested it, I only opened the summary sheet. All my otherlinks actively updated except those using SUMIF. The SUMIF cells display #VALUE!, until I open the detail sheet. Is this the way SUMIF is meant to work between workbooks or is this a "bug" with SUMIF in XL2000 (O/S = XP)? All help is greatly appreciated. -- LPS -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XL2000 - Linking SUMIF with Other Workbook
Hi,
Although I would prefer SUMPRODUCT you can still use SUMIF (not really), for example: =SUM(IF('C:\Users\Shane\Documents\[10-21-2008.xls]Sheet2'!$B$8:$B$17="Dec",'C:\Users\Shane\Documents \[10-21-2008.xls]Sheet2'!$C$8:$C$17,"")) This SUM(IF function works with closed workbooks. It needs to be entered as an array - that means press Shift+Ctrl+Enter to enter it, don't press just Enter. If this helps, please click the Yes button Cheers, Shane Devenshire "LPS" wrote: I have two workbooks. One contains a detailed sheet and the other contains a summary sheet. On the summary sheet I want to use SUMIF so that it goes to the detail sheet and, if a specfiic condition exists in one column, it adds the dollars in an other column, and brings the result back to the summary sheet. When I set it up, both workbooks were open. When I tested it, I only opened the summary sheet. All my otherlinks actively updated except those using SUMIF. The SUMIF cells display #VALUE!, until I open the detail sheet. Is this the way SUMIF is meant to work between workbooks or is this a "bug" with SUMIF in XL2000 (O/S = XP)? All help is greatly appreciated. -- LPS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
workbook linking cells not working within a workbook | Excel Discussion (Misc queries) | |||
Sumif Linking to Another Workbook error #VALUE! | Excel Discussion (Misc queries) | |||
Linking Master Workbook & subordinate workbook | Excel Discussion (Misc queries) | |||
how do i use sumif when linking worksheet | Excel Discussion (Misc queries) | |||
Sumif Linking to Another Workbook error #VALUE! | Excel Discussion (Misc queries) |