Refresh formula links to closed Workbooks

  • Thread starter Thread starter h2fcell
  • Start date Start date
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.
 
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
 
Back
Top