Must open external file to link formula

Hey guys,
We're using SUMIF and it won't work unless the external excel file is open.

This is the formula we're using:

Please Login or Register  to view this content.

Basically, we're scanning Column A (Provider_ID) external file and if any matches with what's in cell B8 in my worksheet, then it will copy corresponding what's in Column C(Beds) in the external file and placing it in my worksheet.

I've been reading that I would change it to SUMPRODUCT or another similar formula that doesn't have this issue but I have not had any luck.


Do you guys have any suggestions?

Thanks

Correct, SUMIF / COUNTIF require target to be open.

A couple of points however regards your existing formula

1 - you don't specify the full path (only the name of the file when open)

2 - you're using 2 Named Ranges in your original for which we don't know the dimensions - so it's not clear if these will cause an issue or not

Based on your original and assuming named ranges are valid and that source file is open when initially creating the replacement then perhaps:

=SUMPRODUCT(--('[Xl0000006.xls]RCF 09'!Provider_ID=B8),'[Xl0000006.xls]RCF 09'!Beds)

If the above works, you can subsequently close the target file and the links should update accordingly (to include full path)

Sorry about that. Here's everything in detail Here's my original formula that works perfectly when all the external files are open:

**Note, it returns 30 when it works correctly

Please Login or Register  to view this content.

It's actually longer but simply repeats itself for more providers when needed. I tried the SUMPRODUCT formula above that you posted but again, no luck.

I played with it and ended up not getting an error, but it did return 0. It should be returning 30.

Please Login or Register  to view this content.

As far as the file path goes, everything is in one folder and that's what shows up when I copy and paste the formula straight from Excel. I know the path is correct because that's how it appears when my first original formula works correctly.
I'm not sure how to get excel to show the full path in the formula bar.

Either way, here's a typed full path if you really care to see it:
L:\RCF6-30-09\Rate Sheets\'[Medstat.xlsx]RCF 09'!

I've also read about people using separate SUM and IF formulas, granted that it will slow down the calculations in the worksheet. Anyway, I tried that but can't get that to work either.

Any ideas?


edit: I realize what I posted yesterday was from Excel 2003 and today was from Excel 2007. Two different computers, but that first formula works correctly in both. I just didn't have access to Excel 2007 when I posted last night.

The SUMPRODUCT you used will not generate a result given you're not coercing the Booleans in the first array as so:

Please Login or Register  to view this content.

pre XL2007 the SUMPRODUCT would return a #NUM error given entire column references can not be used (ie A:A)

My point re: file path is that should you close Medstat.xlsx you will find your formulae alter accordingly ... thus setting up using the shorthand filename with the file closed would not work.

Ok got it. Here it is when the external file is closed:

Please Login or Register  to view this content.

Originally Posted by DonkeyOte

The SUMPRODUCT you used will not generate a result given you're not coercing the Booleans in the first array as so:

Please Login or Register  to view this content.

This one works! Is there anyway you can do the full path just so I can see the full syntax exactly?
Whenever I try it on my own, it doesn't work for me.

This is what I end up when I try using the built in formula "helper"
Mine: (doesn't work)

Please Login or Register  to view this content.

Yours (works)

Please Login or Register  to view this content.

Not really sure what the difference is between that and the code you posted.

I appreciate it.

In my version as mentioned I am coercing the Boolean outputs (True/False) in the first array of values - yours is not - to illustrate

=SUMPRODUCT(1={1,2},{1,2})
=SUMPRODUCT({TRUE,FALSE},{1,2}) -> 0

=SUMPRODUCT(--(1={1,2}),{1,2})
=SUMPRODUCT({1,0},{1,2}) -> 1

or, if you prefer * to double unary (--) then:

=SUMPRODUCT((1={1,2})*{1,2})

Regardless of approach, as pointed out earlier avoid using large ranges with SUMPRODUCT formulae as performance will be affected, keep range sizes to a minimum.

For more info. on SUMPRODUCT (incl. coercion) see the link in my sig. to Bob Phillips' white paper.