Cells not updateing

jeffmoseler

Well-known Member
Joined
Jul 16, 2004
Messages
539
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?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Do you get a #VALUE error? SUMIF doesn't work well with closed workbooks. Try using SUMPRODUCT instead.
 

jeffmoseler

Well-known Member
Joined
Jul 16, 2004
Messages
539
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Brian from Maui said:
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

Well-known Member
Joined
Jul 16, 2004
Messages
539
The last one worked perfectly! You guys are awesome. You saved me so much time!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,236
Messages
5,571,050
Members
412,359
Latest member
misstoffeepenny
Top