Refresh formula links to closed Workbooks
 

Hello,
Im trying to understand why some formulas, that point to other Workbooks,
dont 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 cant get the third formula to
work while €œWard 1€� is closed.

Any suggestions would be greatly appreciated.

Refresh formula links to closed Workbooks
 

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

--
Regards
Roger Govier

"h2fcell" wrote in message
...
Hello,
Im trying to understand why some formulas, that point to other Workbooks,
dont 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 cant get the third formula to
work while €œWard 1€� is closed.

Any suggestions would be greatly appreciated.