sumif function
Posted by Jim Sheldon on March 06, 2001 10:09 AM
I am using the sumif function to read values out of another workbook
that resides on our companies server. The function only seems to work
correctly when the referenced file is open. I thought that function
should work without the file being opened. Any ideas?
Posted by Dave Hawley on March 06, 2001 10:14 AM
Hi Jim
It should still work if the file is not open. What do you mean by "only seems to work
correctly" ?
Is the referenced file password protected or Read Only ?
Dave
OzGrid Business Applications
Posted by Jim Sheldon on March 06, 2001 10:44 AM
When the criteria field is a constant and the referenced file is open, the function works.
When the criteria field is a constant and referenced file is not open, the function will
retain the last saved value. If you try to recalculate, while the referenced file is
closed, "# Value" is the value I will get. The file is not password protectected nor
read-only.
Jim
Posted by Dave Hawley on March 06, 2001 11:09 AM
Looks like SUMIF needs the file to be open. You can use an array formula to get the same result though:
=SUM(IF('C:\[MecureUserform.xls]RECONCILATION'!$C$5:$C$15>1,'C:\[MecureUserform.xls]RECONCILATION'!$C$5:$C$15))
and enter with Ctr+Shift+Enter
Dave
OzGrid Business Applications
