#value! error

[srshort62]

I have entered the following array formula, it works fine as long as I have
the linked files open; however, if I don't then I get the #value error and
also the error message that the string is too long. How can I correct this?

=IF(A9="","",(SUMIF('[Bid Item Quantity Sheet Contract Line Item 10.xls]BID
ITEM QUANTITY SHEET'!$C$7:$C$65536,"<"&VLOOKUP($G$3,'F:\User Settings\My
Documents\My Projects\Route 60 - B686 (Contract I)\[5 C3P Inspector''s
Monthly Construction Progress Report.xls]TEMPORARY
SHUTDOWNS'!11:65336,2),'[Bid Item Quantity Sheet Contract Line Item
10.xls]BID ITEM QUANTITY SHEET'!$E$7:$E$65536)-(SUMIF('[Bid Item Quantity
Sheet Contract Line Item 10.xls]BID ITEM QUANTITY
SHEET'!$C$7:$C$65536,"<"&VLOOKUP($G$3,'F:\User Settings\My Documents\My
Projects\Route 60 - B686 (Contract I)\[5 C3P Inspector''s Monthly
Construction Progress Report.xls]TEMPORARY SHUTDOWNS'!11:65336,2),'[Bid Item
Quantity Sheet Contract Line Item 10.xls]BID ITEM QUANTITY
SHEET'!$K$7:$K$65536))))

 

[FloMM2]

srshort62,
Have you tried identifying all you linked files as you did for " 'F:\User
Settings\My Documents\My Projects\Route 60 - B686 (Contract I)\[5 C3P
Inspector"s Monthly Construction Progress Report.xls]"
I think this will solve your problem. You have them linked, but if they are
not OPEN,
excel can't find them.
hth

 

[Dave Peterson]

There are some functions that won't work unless the sending file is open.
=sumif(), =countif(), =indirect()
are a few.

You could replace the formula with the equivalent =sumproduct()

=SUMPRODUCT(--([book1.xls]Sheet1!$A$5:$A$1000="Something"),
[book1.xls]Sheet1!F$5:F$1000)
......

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I have entered the following array formula, it works fine as long as I have
the linked files open; however, if I don't then I get the #value error and
also the error message that the string is too long. How can I correct this?

=IF(A9="","",(SUMIF('[Bid Item Quantity Sheet Contract Line Item 10.xls]BID
ITEM QUANTITY SHEET'!$C$7:$C$65536,"<"&VLOOKUP($G$3,'F:\User Settings\My
Documents\My Projects\Route 60 - B686 (Contract I)\[5 C3P Inspector''s
Monthly Construction Progress Report.xls]TEMPORARY
SHUTDOWNS'!11:65336,2),'[Bid Item Quantity Sheet Contract Line Item
10.xls]BID ITEM QUANTITY SHEET'!$E$7:$E$65536)-(SUMIF('[Bid Item Quantity
Sheet Contract Line Item 10.xls]BID ITEM QUANTITY
SHEET'!$C$7:$C$65536,"<"&VLOOKUP($G$3,'F:\User Settings\My Documents\My
Projects\Route 60 - B686 (Contract I)\[5 C3P Inspector''s Monthly
Construction Progress Report.xls]TEMPORARY SHUTDOWNS'!11:65336,2),'[Bid Item
Quantity Sheet Contract Line Item 10.xls]BID ITEM QUANTITY
SHEET'!$K$7:$K$65536))))