sumproduct on a closed workbook with dynamic sheet and file names

[mark_snowdowne]

Thanks for reading.
I'm using the sumproduct function to sumif a closed excel workbook.

This works fine:

=SUMPRODUCT(
--('[Civil Estimate - Division 2.xlsm]Div. 2'!$AE:$AE=$C1),
'[Civil Estimate - Division 2.xlsm]Div. 2'!$Y:$Y)

What I want to do is have dynamic tab names and if possible dynamic file names.

The files are called "Estimate - Division 2", "Estimate - Division 3", etc.
They each have sheets in them named Div. 1, Div. 2, etc.

cell C1 = lookup code

cell S1 = Division #

My attempt:

=SUMPRODUCT(
--(INDIRECT("'C:\4. Files\[Estimate Division "&S1&".xlsm]Div. "&S1&"'!$AE:$AE")=$C1),
(INDIRECT("'C:\4. Files\[Estimate - Division "&S1&".xlsm]Div. "&S1&"'!$Y:$Y")))

cell C1 = lookup code

cell S1 = Division #

It works, but only if the workbook is open... which defeats the purpose of using sum product to mimic a sumif funciton.

I'm terrible with indirect functions. Any help is appreciated. Thanks!

[#2]

@mark_snowdowne 

Unfortunately, the INDIRECT function does not support references to closed workbooks...

[#3]

Thanks.

I suppose there is no other way to accomplish this?
Using the &" "& concatenation doesn't seem to work.

@Hans Vogelaar 

[#4]

@mark_snowdowne 

No, INDIRECT won't work whichever way you try.