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.

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.