Wonder if INDIRECT is the right thing to use here...(probably not)
 

Question,

I use the following formula.

=SUMIFS(INDIRECT("'"&$D$1&"["&$D8&" "&$D$2&"$H$10:$H$2000"),INDIRECT("'"&$D$1&"["&$D8&" "&$D$2&"$J$10:$J$2000"),F$5&F$6,INDIRECT("'"&$D$1& "["&$D8&" "&$D$2&"$U$10:$U$2000"),$B8)

(For info; cells D1 and D2 contain text strings which relate to the filepath and file name. The completed filepath & filname is achieved by piecing in some variables between D1 and D2).

The formuale refers to excel files other than the "live" one into which the above formula operates. The whole thing works fine when the 10 or so spreadsheets it refers to are open.

I'd like the formula to work if the other spreadsheets are not open.

Can anyone advise me what adjustmenets I need to make to the formula.

Thanks

Gerry.