Forum Discussion

ACB-COL's avatar
ACB-COL
Copper Contributor
Nov 08, 2020

Multiple excel spreadsheets

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...

 

 

 

Formulas and Functions

1 Reply

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Nov 09, 2020

    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.

Resources