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

Advertisements

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
 

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

Top