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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

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,123,239
Messages
5,600,486
Members
414,383
Latest member
konmtu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top