Excel #Value! Error on external link, when source file closed

[Erin]

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!!

 

[Dave Peterson]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Erin]

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

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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!!

 

[Dave Peterson]

If the file is open, then the =sumif() functions will work.

If you close the file, but haven't recalculated, then the results look ok. But
as soon as you recalc, you'll see an error.

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

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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!!