It's not the UNC path that's the problem. The problem is that =sumif() won't
work when the sending file is closed.
But there are alternatives.
One of them is =sumproduct()
=sumproduct(--(longstring!$f$2:$f$99999=$b42&$c42),longstring!$g $2:$g$99999)
Adjust the ranges to match--but you can't use whole columns (except in xl2007).
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
=========
ps. I'd open the sending workbook before I created the formula. When I have it
correct and close that workbook excel will include the path.
pps. Open the file via the UNC path, too.
Bruce wrote:
To ensure that linkages in my spreadsheet will work for other users on our
shared folders, I have used the full pathname, or UNC name, in my formulas.
It works fine for VLOOKUP, but for some reason I get the #VALUE! error when
using SUMIF in Excel 2007. I'm not sure what I'm doing incorrectly, or
simply that using the UNC name will not work in SUMIF.
Here's a sample of the syntax:
SUMIF('\\hyperdisk\data\accounting and finance\budgets\2008 CFO
Report\[PriorActual.xlsm]January'!$F$2:$F$99999,$B42&$C42,'\\hyperdisk\data \accounting
and finance\budgets\2008 CFO Report\[PriorActual.xlsm]January'!$G$2:$G$99999)
--
Dave Peterson