Home |
Search |
Today's Posts |
#1
|
|||
|
|||
#VALUE! On An Array Formula Referencing a Range Outside The Workbo
This formula works only when i have the other referenced workbook open
simultaneously...when it is not open, i get the #VALUE! error.... Array Formula: {=SUM(IF($F$5:$F$92=$F280,O$5:O$92))+SUMIF('O:\Gru ss Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95,$F280,'O:\Gruss Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!M:M)} |
#2
|
|||
|
|||
There are some functions that don't work with closed workbooks. You found one
with =sumif(). But you could use a different formula to replace the =sumif() portion: =sumproduct(--('yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95=$f280), 'yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$m$1:$m$95) paige wrote: This formula works only when i have the other referenced workbook open simultaneously...when it is not open, i get the #VALUE! error.... Array Formula: {=SUM(IF($F$5:$F$92=$F280,O$5:O$92))+SUMIF('O:\Gru ss Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95,$F280,'O:\Gruss Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!M:M)} -- Dave Peterson |
#3
|
|||
|
|||
Ps. I'd open that other workbook and build the formula by pointing and
clicking. Excel will add the path when you close the workbook. And you won't be able to use the whole column in that formula. paige wrote: This formula works only when i have the other referenced workbook open simultaneously...when it is not open, i get the #VALUE! error.... Array Formula: {=SUM(IF($F$5:$F$92=$F280,O$5:O$92))+SUMIF('O:\Gru ss Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95,$F280,'O:\Gruss Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!M:M)} -- Dave Peterson |
#4
|
|||
|
|||
INTERESTING....THANKS. NEVER HEARD OF SUMPRODUCT....I LOVE LEARNING NEW ****.
THANKS AGAIN! ;O) "Dave Peterson" wrote: There are some functions that don't work with closed workbooks. You found one with =sumif(). But you could use a different formula to replace the =sumif() portion: =sumproduct(--('yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95=$f280), 'yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$m$1:$m$95) paige wrote: This formula works only when i have the other referenced workbook open simultaneously...when it is not open, i get the #VALUE! error.... Array Formula: {=SUM(IF($F$5:$F$92=$F280,O$5:O$92))+SUMIF('O:\Gru ss Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95,$F280,'O:\Gruss Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!M:M)} -- Dave Peterson |
#5
|
|||
|
|||
thanks...but how do i incorporate the if part of my formula? essentially, i
only need it to sum only when a condition is met.....do a sumproduct with a match statement? thoughts? "Dave Peterson" wrote: There are some functions that don't work with closed workbooks. You found one with =sumif(). But you could use a different formula to replace the =sumif() portion: =sumproduct(--('yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95=$f280), 'yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$m$1:$m$95) paige wrote: This formula works only when i have the other referenced workbook open simultaneously...when it is not open, i get the #VALUE! error.... Array Formula: {=SUM(IF($F$5:$F$92=$F280,O$5:O$92))+SUMIF('O:\Gru ss Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95,$F280,'O:\Gruss Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!M:M)} -- Dave Peterson |
#6
|
|||
|
|||
paige wrote...
thanks...but how do i incorporate the if part of my formula? essentially, i only need it to sum only when a condition is met.....do a sumproduct with a match statement? thoughts? .... Reread what Dave told you to do. MATCH isn't needed. SUMIF won't function if either its 1st or 3rd arguments are external link references into closed workbooks. You can replace function calls like SUMIF(extref1,X,extref2) with SUMPRODUCT calls like SUMPRODUCT(--(extref1=X),extref2) Replace the SUM(IF(..)) part of your formula with a SUMIF, SUMIF($F$5:$F$92,$F280,O$5:O$92) and replace the SUMIF part with SUMPRODUCT, at which point it wouldn't need to be entered as an array formula. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What's wrong with my array formula | Excel Discussion (Misc queries) | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
how to enter a formula using column() function for a range | Excel Worksheet Functions |