Hello Dev,
i had the same problem with sumifs and with lots of source excel files. Your solution helped me a lot as i do not need to open up n number of source files eveytime i want to update the destination.

Thank you!


reg,

Manish

On Sunday, July 13, 2008 7:58 PM Eri wrote:

Hi All,

looking for a resolution - I don't know why this is happening, but I'm using
SUMIF formulae that references external links (source data is in external
file). The correct value is retrieved when the source file is open, however,
when the workbook is closed, I get a #VALUE error. Since I have heaps of
formulae in this workbook (and a couple of others) relying on this external
source file, I want to make sure that I can view the correct values when the
source file is closed. Also - if I send the report to team members, I want
them to be able to view the data without having to open the source file.
Any ideas on how to get this link to work when the source file(s) is closed?

Thanks!!


On Sunday, July 13, 2008 8:38 PM Dave Peterson wrote:

There are some functions that won't work unless the sending file is open.
=sumif(), =countif(), =indirect()
are a few.

You could replace the formula with the equivalent =sumproduct()

=SUMPRODUCT(--([book1.xls]Sheet1!$A$5:$A$1000="Something"),
[book1.xls]Sheet1!F$5:F$1000)
.....

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Erin wrote:

--

Dave Peterson


On Sunday, July 13, 2008 11:46 PM Eri wrote:

Hi Dave, thanks for your help. But why do some of my SumIF's work when
referencing that file, and not others? The 255 character rule doesn't seem
to apply, since some of the failures were for cells that contained less than
255 characters.

I've used the solution you've provided for the cells that were erroring out.
Hopefully the others will stay 'ok'. I've never used the sumproduct formula
before, and the link you provided gives some v. useful information on it...

Thanks!
Erin

"Dave Peterson" wrote: