# Refresh formula links to closed Workbooks

H

#### 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.

R

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