#VALUE error when linking to other files

I have a summary sheet that pulls info from approx 10 other excel
files.

In one column of the summary sheet, the links perform just fine. Each
cell in this column is the sum of an entire column in another
workbook. When I open the summary page and choose "yes" to update
with new information, everything works properly.

However, in the same worksheet as this, I have another column where
each cell is the result of a SUMIF where the data comes from another
workbook. The odd part that I cannot understand is why when I open
the summary sheet and update the information do all the cells in that
column get a #VALUE error, while the other column that references the
same workbook comes out properly?

And the real kicker is that as soon soon as I individually open each
referenced workbook, the #VALUE errors fill in with the correct
information.

What is going on here?

Thanks!

There are some functions that don't work with closed workbooks. =sumif() is one
of them.

But there are workarounds.

Saved from a previous post:

Use a different function...

Maybe =sum(if(...)) or =sumproduct() will work ok for you

Maybe you'll see how you can modify your existing formula:


{=SUM(IF('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green",
'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10))}

=sumproduct(--('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green"),
'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10)

If this didn't help, post back with your existing formula.

by the way, the =sum(if(...
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

"Jerome81 - ExcelForums.com" wrote:
>
> I have a summary sheet that pulls info from approx 10 other excel
> files.
>
> In one column of the summary sheet, the links perform just fine. Each
> cell in this column is the sum of an entire column in another
> workbook. When I open the summary page and choose "yes" to update
> with new information, everything works properly.
>
> However, in the same worksheet as this, I have another column where
> each cell is the result of a SUMIF where the data comes from another
> workbook. The odd part that I cannot understand is why when I open
> the summary sheet and update the information do all the cells in that
> column get a #VALUE error, while the other column that references the
> same workbook comes out properly?
>
> And the real kicker is that as soon soon as I individually open each
> referenced workbook, the #VALUE errors fill in with the correct
> information.
>
> What is going on here?
>
> Thanks!

--

Dave Peterson