I've got this formula:
=SUMPRODUCT(--('I:\IWS KPI\[All Approved Between
Dates.Xls]Sheet1'!$B$2:$B$10000="IWS"),--('I:\IWS KPI\[All Approved Between
Dates.Xls]Sheet1'!$C$2:$C$10000=Sheet2!B2),--('I:\IWS KPI\[All Approved
Between Dates.Xls]Sheet1'!$C$2:$C$10000<=Sheet2!B3),--('I:\IWS KPI\[All
Approved Between Dates.Xls]Sheet1'!$D$2:$D$10000="Approved"))
in an xls file called Report.xls (in the same location as the referenced
file). It only seems to want to update this formula when the other sheet is
open. If it isn't it says 'Unable to open file'. Can anyone help? I've got
to do similar formulae for other KPI xls files in the same Report sheet and
don't want to have to open EVERY other file for this to calculate.