sumproduct on a closed workbook with dynamic sheet and file names

%3CLINGO-SUB%20id%3D%22lingo-sub-1626295%22%20slang%3D%22en-US%22%3Esumproduct%20on%20a%20closed%20workbook%20with%20dynamic%20sheet%20and%20file%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1626295%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20reading.%3CBR%20%2F%3EI'm%20using%20the%20sumproduct%20function%20to%20%3CA%20href%3D%22https%3A%2F%2Fwww.excelforum.com%2Fexcel-formulas-and-functions%2F1060824-sumifs-returning-value-on-closed-workbook.html%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Esumif%20a%20closed%20excel%20workbook.%3C%2FA%3E%3C%2FP%3E%3CP%3EThis%20works%20fine%3A%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3DSUMPRODUCT(%3CBR%20%2F%3E--('%5BCivil%20Estimate%20-%20Division%20%3CFONT%20color%3D%22%23000000%22%3E%3CFONT%20color%3D%22%230000FF%22%3E2%3C%2FFONT%3E.%3C%2FFONT%3Exlsm%5DDiv.%20%3CFONT%20color%3D%22%230000FF%22%3E2%3C%2FFONT%3E'!%24AE%3A%24AE%3D%24C1)%2C%3CBR%20%2F%3E'%5BCivil%20Estimate%20-%20Division%20%3CFONT%20color%3D%22%230000FF%22%3E2%3C%2FFONT%3E.xlsm%5DDiv.%20%3CFONT%20color%3D%22%230000FF%22%3E2%3C%2FFONT%3E'!%24Y%3A%24Y)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20want%20to%20do%20is%20have%20dynamic%20tab%20names%20and%20if%20possible%20dynamic%20file%20names.%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EThe%20files%20are%20called%20%22Estimate%20-%20Division%202%22%2C%26nbsp%3B%22Estimate%20-%20Division%203%22%2C%20etc.%3CBR%20%2F%3EThey%20each%20have%20sheets%20in%20them%20named%20Div.%201%2C%20Div.%202%2C%20etc.%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3Ecell%20C1%20%3D%20lookup%20code%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3Ecell%20S1%20%3D%20Division%20%23%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CBR%20%2F%3EMy%20attempt%3A%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3DSUMPRODUCT(%3CBR%20%2F%3E--(INDIRECT(%22'C%3A%5C4.%20Files%5C%5BEstimate%20Division%20%3CFONT%20color%3D%22%230000FF%22%3E%22%26amp%3BS1%26amp%3B%22%3C%2FFONT%3E.xlsm%5DDiv.%20%22%3CFONT%20color%3D%22%230000FF%22%3E%26amp%3BS1%26amp%3B%22%3C%2FFONT%3E'!%24AE%3A%24AE%22)%3D%24C1)%2C%3CBR%20%2F%3E(INDIRECT(%22'C%3A%5C4.%20Files%5C%5BEstimate%20-%20Division%20%3CFONT%20color%3D%22%230000FF%22%3E%22%26amp%3BS1%26amp%3B%22%3C%2FFONT%3E.xlsm%5DDiv.%20%3CFONT%20color%3D%22%230000FF%22%3E%22%26amp%3BS1%26amp%3B%22%3C%2FFONT%3E'!%24Y%3A%24Y%22)))%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3Ecell%20C1%20%3D%20lookup%20code%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3Ecell%20S1%20%3D%20Division%20%23%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EIt%20works%2C%20but%20only%20if%20the%20workbook%20is%20open...%20which%20defeats%20the%20purpose%20of%20using%20sum%20product%20to%20mimic%20a%20sumif%20funciton.%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20terrible%20with%20indirect%20functions.%20Any%20help%20is%20appreciated.%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1626295%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1626504%22%20slang%3D%22en-US%22%3ERe%3A%20sumproduct%20on%20a%20closed%20workbook%20with%20dynamic%20sheet%20and%20file%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1626504%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F778681%22%20target%3D%22_blank%22%3E%40mark_snowdowne%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUnfortunately%2C%20the%20INDIRECT%20function%20does%20not%20support%20references%20to%20closed%20workbooks...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1627652%22%20slang%3D%22en-US%22%3ERe%3A%20sumproduct%20on%20a%20closed%20workbook%20with%20dynamic%20sheet%20and%20file%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1627652%22%20slang%3D%22en-US%22%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3EI%20suppose%20there%20is%20no%20other%20way%20to%20accomplish%20this%3F%3CBR%20%2F%3EUsing%20the%20%26amp%3B%22%20%22%26amp%3B%20concatenation%20doesn't%20seem%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1627745%22%20slang%3D%22en-US%22%3ERe%3A%20sumproduct%20on%20a%20closed%20workbook%20with%20dynamic%20sheet%20and%20file%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1627745%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F778681%22%20target%3D%22_blank%22%3E%40mark_snowdowne%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENo%2C%20INDIRECT%20won't%20work%20whichever%20way%20you%20try.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

@mark_snowdowne 

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

Thanks.

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

 

@Hans Vogelaar 

@mark_snowdowne 

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