SUMIF problem

[Aussie CPA]

I have a spreadsheet with the following SUMIF formula, which looks at another
(Large)workbook on our network.

=SUMIF('W:\HF\2005\Payroll\JAN 05\[Leave Liability_Staff_January.xls]Journal
Data'!$D$17:$D$65536,CD114,'W:\HF\2005\Payroll\JAN 05\[Leave
Liability_Staff_January.xls]Journal Data'!$H$17:$H$65536)

My problem is if I recalculate the formula without the linked sheet open I
get a #VALUE message. Is this an Excel bug or am I doing something wrong?

Thanks

 

[RagDyer]

Not a bug.
Some functions, Indirect, Sumif, Countif, among others, just *do not* work
on closed WBs.

There are various alternatives, SumProduct() being one.

Try this:

=SUMPRODUCT(('W:\HF\2005\Payroll\JAN 05\[Leave
Liability_Staff_January.xls]Journal
Data'!D$17:$D$65536=CD114)*'W:\HF\2005\Payroll\JAN 05\[Leave
Liability_Staff_January.xls]Journal Data'!$H$17:$H$65536)

Watch out for the word wrap.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have a spreadsheet with the following SUMIF formula, which looks at
another
(Large)workbook on our network.

=SUMIF('W:\HF\2005\Payroll\JAN 05\[Leave Liability_Staff_January.xls]Journal
Data'!$D$17:$D$65536,CD114,'W:\HF\2005\Payroll\JAN 05\[Leave
Liability_Staff_January.xls]Journal Data'!$H$17:$H$65536)

My problem is if I recalculate the formula without the linked sheet open I
get a #VALUE message. Is this an Excel bug or am I doing something wrong?

Thanks