ExcelBanter - External xls reference error

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - External xls reference error (https://www.excelbanter.com/excel-worksheet-functions/231674-external-xls-reference-error.html)

External xls reference error

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.

External xls reference error

Sorry error is 'Unable to read file' not 'unable to open file'.

"Fluke" wrote:

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.