Refresh formula links to closed Workbooks

[h2fcell]

Hello,
I’m trying to understand why some formulas, that point to other Workbooks,
don’t update automatically in Excel 2007.
I have two workbooks “test†and “Ward 1â€.
“Ward 1†is my source workbook.
In “test†I have the following three formulas in separate cells.
=+'C:\Work\[Ward 1.xlsm]Ward 1'!$P$37
=SUM('C:\Work\[Ward 1.xlsm]Ward 1'!$P$7:$P$37)
=COUNTIF('C:\Work\[Ward 1.xlsm]Ward 1'!$D$4:$N$4,"x")

The first two update automatically weather “Ward 1†is open or closed.
The last one updates only when “Ward 1†is open at the same time as “testâ€.
When “Ward 1†is closed, the third formula returns #VALUE!

No matter what I do in Data/Connections I can’t get the third formula to
work while “Ward 1†is closed.

Any suggestions would be greatly appreciated.

 

[Roger Govier]

Hi

Neither Sumif or Countif will work with closed workbooks.
Either construct an array formula using IF and COUNT or use SUMPRODUCT

=SUMPRODUCT(--('C:\Work\[Ward 1.xlsm]Ward 1'!$D$4:$N$4="x"))

Incidentally you do not need the + sign in your first formula, Simply
='C:\Work\[Ward 1.xlsm]Ward 1'!$P$37
will suffice