Multiple excel spreadsheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1865088%22%20slang%3D%22en-US%22%3EMultiple%20excel%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1865088%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20it%20possible%20to%20have%20a%20list%20of%20excel%20spreadsheets%20stored%20in%20cells%20on%20one%20sheet%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirstworkbook1.xls%3C%2FP%3E%3CP%3ESecondworkbook1.xls%3C%2FP%3E%3CP%3EThirdworkbook1.xls%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20then%20use%20the%20SUMPRODUCT()%20function%20to%20access%20each%20spreadsheet%20in%20turn%20to%20look%20for%20similar%20data%20without%20having%20to%20manually%20retype%20each%20worksheet%20name%20into%20the%20SUMPRODUCT()%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI.E.%20I%20have%20maybe%20200%20or%20so%20spreadsheets...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1865088%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1866115%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20excel%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1866115%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F861686%22%20target%3D%22_blank%22%3E%40ACB-COL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Faveraging-the-same-cell-across-multiple-sheets%2Fm-p%2F1817531%22%20target%3D%22_self%22%3ECheck%20this%20link%20for%20details%2C%2C%2C%3C%2FA%3E%26nbsp%3Brecently%20I've%20solved%20the%20similar%20issue%2C%2C%20what%20you%20have%20to%20do%20is%2C%2C%20replace%20AVERAGE%20with%20an%20appropriate%20SUMPRODUCT%20formula%2C%2C%20in%20case%20find%20issue%2C%20please%20drop%20a%20message%20or%20reply%20this%20post.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EHere%20is%20few%20sample%20formula%3A%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMPRODUCT(SUMIF(INDIRECT(%22'%22%26amp%3BA1%3AA4%26amp%3B%22'!%22%26amp%3B%22D1%3AD25%22)%2CA10%2CINDIRECT(%22'%22%26amp%3BA1%3AA4%26amp%3B%22'!%22%26amp%3B%22E1%3AE25%22)))%0A%0ANote%3A%20A1%3AA4%20is%20range%20does%20have%20sheet%20name.%0A%20%20%20%20%20%20A10%20is%20cell%20contents%20criteria.%20%0AOR%0A%0A%3DSUMPRODUCT(SUMIF(INDIRECT(%22'%22%26amp%3BSheetName%26amp%3B%22'!%22%26amp%3B%22D1%3AD25%22)%2CA10%2CINDIRECT(%22'%22%26amp%3BSheetNAme%26amp%3B%22'!%22%26amp%3B%22E1%3AE25%22)))%0A%0ANote%3A%20SheetName%20is%20Named%20Range%2C%2C%20for%20A1%3AA4%20does%20have%20sheet%20name%20and%20A10%20is%20cell%20contents%20criteria.%20%0A%20%20%20%20%20%0AOR%0A%0ASUMPRODUCT(SUMIFS(INDIRECT(%22'%22%20%26amp%3B%20SheetName%20%26amp%3B%20%22'!%24D%3A%24D%22)%2CINDIRECT(%22'%22%20%26amp%3B%20SheetName%20%26amp%3B%20%22'!%24A%3A%24A%22)%2CA10%2CINDIRECT(%22'%22%20%26amp%3B%20Sheets%20%26amp%3B%20%22'!%24B%3A%24B%22)%2CB10))%0A%0ANote%3A%20A10%20%26amp%3B%20B10%20are%20cells%20contents%20criteria.%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EAdjust%20cell%20references%20and%20others%20as%20needed.%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Is it possible to have a list of excel spreadsheets stored in cells on one sheet:

 

Firstworkbook1.xls

Secondworkbook1.xls

Thirdworkbook1.xls

 

And then use the SUMPRODUCT() function to access each spreadsheet in turn to look for similar data without having to manually retype each worksheet name into the SUMPRODUCT() function.

 

I.E. I have maybe 200 or so spreadsheets...

 

 

 

1 Reply

@ACB-COL 

 

Check this link for details,,, recently I've solved the similar issue,, what you have to do is,, replace AVERAGE with an appropriate SUMPRODUCT formula,, in case find issue, please drop a message or reply this post.  

 

  • Here is few sample formula:

 

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A4&"'!"&"D1:D25"),A10,INDIRECT("'"&A1:A4&"'!"&"E1:E25")))

Note: A1:A4 is range does have sheet name.
      A10 is cell contents criteria. 
OR

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetName&"'!"&"D1:D25"),A10,INDIRECT("'"&SheetNAme&"'!"&"E1:E25")))

Note: SheetName is Named Range,, for A1:A4 does have sheet name and A10 is cell contents criteria. 
     
OR

SUMPRODUCT(SUMIFS(INDIRECT("'" & SheetName & "'!$D:$D"),INDIRECT("'" & SheetName & "'!$A:$A"),A10,INDIRECT("'" & Sheets & "'!$B:$B"),B10))

Note: A10 & B10 are cells contents criteria.

 

 

Adjust cell references and others as needed.