Don,

I defined names and entered the following formula with the same results. I
get #Ref in the cell until I open the other workbook. Great tip on making
the formula shorter and more readable. Any other tips on what may help?

=SUMPRODUCT(--(RcolH=A2),--(MID(RcolG,7,5)="80801"),RcolF)


"Don Guillett" wrote:

Try using a defined name in the destination file that refers to range in the
source file. It will also make your formulas easier

RcolH
=[Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Vince" wrote in message
...
The formula below only updates the cell if the worksheet
"Requisitions.xls"
is open. Otherwise, I get the message to update the link when I open the
current worksheet but all that is displayed is #REF in each cell that the
formula exists. Is there a way around this? I would like not to have to
open the other workbook in order for the cells to update.

I am summing all the values in Requisitions.xls column F where column H =
the value in A2 and column G = 80801. Any help or suggestions you have
are
appreciated.

=SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553=A2),--(MID([Requisitions.xls]Requisitions!$G$6:[Requisitions.xls]Requisitions!$G$6553,7,5)="80801"),[Requisitions.xls]Requisitions!$F$6:[Requisitions.xls]Requisitions!$F$6553)