Refresh formula links to closed Workbooks



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


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question