Hi

Into your workbook add a (hidden) worksheet with duplicate of your source
data in external workboook (use links for this, and duplicate only columns
you need). Now rewrite your formulas, so they refer to this (hidden) sheet.

Example:
Create sheet Links
Links!A1=IF([Requisitions.xls]Requisitions!$H6="","",[Requisitions.xls]Requisitions!$H6)
Links!B1=IF([Requisitions.xls]Requisitions!$F6="","",[Requisitions.xls]Requisitions!$F6)
Links!C1=IF([Requisitions.xls]Requisitions!$G6="","",[Requisitions.xls]Requisitions!$G6)

Copy Links!A1:C1 down for at least as much rows as in your source table.

Your formula will be
=SUMPRODUCT(--(Links!$A$1:$A$6547=A2),--(MID(Links!$C$1:$C$6547,7,5)="80801"),Links!$B$1:$ B$6547)



Arvi Laanemets



"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)