Cells not updateing

[jeffmoseler]

I have an Excel spreadsheet linked to another spreadsheet that is giving me errors. The formula looks like this:

=IF(E3="","",(SUMIF('C:\Documents and Settings\Jeff\My Documents\B & G\[QB Report.xls]2006 Sales'!$K$3:$K$1000,E3,'C:\Documents and Settings\Jeff\My Documents\B & G\[QB Report.xls]2006 Sales'!$U$3:$U$1000)))

If I open the spreadsheet it asks if I want to update the data and I say yes. All the other formulas update with out a problem, but this one gives me a NAME error. When I open the connected spreadsheet the error goes away and the data fills in. If I close the connected spreadsheet, the error does not come back and the data stays there. How do I make this work without having to open and close the spreadsheet?

 

[Brian from Maui]

Do you get a #VALUE error? SUMIF doesn't work well with closed workbooks. Try using SUMPRODUCT instead.

 

[jeffmoseler]

Correction

Yes it is a #Value error not a NAME error.

I not sure how I would use the SUMPRODUCT formula to do the same thing as a SUMIF. Basically what I am trying to do is this:

Take the value in E3 and look for it in column K of another spreadsheet, if you find it, add the number in column U.
Can you do something like that with a SUMPRODUCT?

 

[Brian from Maui]

Try,

=Sumproduct(--(SUMIF('C:\Documents and Settings\Jeff\My Documents\B & G\[QB Report.xls]2006 Sales'!$K$3:$K$1000,E3,'C:\Documents and Settings\Jeff\My Documents\B & G\[QB Report.xls]2006 Sales'!$U$3:$U$1000))

 

[Aladin Akyurek]

Try,

=Sumproduct(--(SUMIF('C:\Documents and Settings\Jeff\My Documents\B & G\[QB Report.xls]2006 Sales'!$K$3:$K$1000,E3,'C:\Documents and Settings\Jeff\My Documents\B & G\[QB Report.xls]2006 Sales'!$U$3:$U$1000))

I think you meant replacing SumIf with SumProduct when it is needed to work with closed workbooks:

=SUMPRODUCT(--('C:\Documents and Settings\Jeff\My Documents\B & G\[QB Report.xls]2006 Sales'!$K$3:$K$1000=E3),'C:\Documents and Settings\Jeff\My Documents\B & G\[QB Report.xls]2006 Sales'!$U$3:$U$1000)

 

[jeffmoseler]

The last one worked perfectly! You guys are awesome. You saved me so much time!