sumproduct on a closed workbook with dynamic sheet and file names

Highlighted
New Contributor

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!

3 Replies
Highlighted

@mark_snowdowne 

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

Highlighted

Thanks.

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

 

@Hans Vogelaar 

Highlighted

@mark_snowdowne 

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