I have numerous sumif formulas in a workbook (see example below), and they
reference other workbooks. When I open the workbook with the formulas in it,
they all revert to #value! unless the linked workbook is open. This did not
happen in previous versions of excel (I use 2003). Just wondering if anyone
knows if this still happens in 2007? It can be a pain to open 8 to 10
workbooks just to get the formula to give me a correct number again. thanks.

=ROUND(SUMIF('W:\USERS\Choi\C24\JLC\Inventory\LIFO
Project\Target\FYE013104\[FYE013104Depts_by_Pool.xls]GM
Depts'!$B$8:$B$1500,$A9,'W:\USERS\Choi\C24\JLC\Inv entory\LIFO
Project\Target\FYE013104\[FYE013104Depts_by_Pool.xls]GM
Depts'!$I$8:$I$1500),0)

SUMIF doesn't work when referencing a closed file. Use SUMPRODUCT instead.
It works on closed files.

=ROUND(SUMPRODUCT(--('W:\USERS\Choi\C24\JLC\Inventory\LIFO
Project\Target\FYE013104\[FYE013104Depts_by_Pool.xls]GM
Depts'!$B$8:$B$1500=$A9),'W:\USERS\Choi\C24\JLC\In ventory\LIFO
Project\Target\FYE013104\[FYE013104Depts_by_Pool.xls]GM
Depts'!$I$8:$I$1500),0)


--
Biff
Microsoft Excel MVP


"JLC1952" wrote in message
...
I have numerous sumif formulas in a workbook (see example below), and they
reference other workbooks. When I open the workbook with the formulas in
it,
they all revert to #value! unless the linked workbook is open. This did
not
happen in previous versions of excel (I use 2003). Just wondering if
anyone
knows if this still happens in 2007? It can be a pain to open 8 to 10
workbooks just to get the formula to give me a correct number again.
thanks.

=ROUND(SUMIF('W:\USERS\Choi\C24\JLC\Inventory\LIFO
Project\Target\FYE013104\[FYE013104Depts_by_Pool.xls]GM
Depts'!$B$8:$B$1500,$A9,'W:\USERS\Choi\C24\JLC\Inv entory\LIFO
Project\Target\FYE013104\[FYE013104Depts_by_Pool.xls]GM
Depts'!$I$8:$I$1500),0)