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

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:

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

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:

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:

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.

Erin 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:

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:

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


--

Dave Peterson

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:

Hi there,

The #VALUE error occurs when Excel is unable to interpret a formula or function. In this case, it seems like the issue is with the external link to the source file. When the source file is closed, Excel is unable to retrieve the data required for the SUMIF formula, resulting in the #VALUE error.

To resolve this issue, you can try the following steps:

1. Open the source file and the workbook that contains the SUMIF formula.
2. In the workbook that contains the SUMIF formula, click on the cell that contains the formula.
3. In the formula bar, click on the external link to the source file. This will highlight the link.
4. Right-click on the link and select "Edit Link".
5. In the "Edit Links" dialog box, select the link to the source file and click on "Change Source".
6. In the "Change Source" dialog box, navigate to the location of the source file and select it.
7. Click on "Open" and then "OK" to close the dialog boxes.

This should update the link to the source file and allow Excel to retrieve the data required for the SUMIF formula, even when the source file is closed.

Alternatively, you can try copying the data from the source file and pasting it into the workbook that contains the SUMIF formula. This will eliminate the need for an external link and ensure that the data is always available, even when the source file is closed.