Forum Discussion

JoJo1's avatar
JoJo1
Copper Contributor
Oct 01, 2025
Solved

Drawing data from multiple spreadsheets

Hi, So I have multiple spreadsheets with animal numbers and their weights I want to combine it on one spreadsheet The problem is that not all the animal numbers are on all the tabs, so I need ...
excel
Formulas and Functions
office 365
  • OliverScheurich's avatar
    OliverScheurich
    Oct 01, 2025

    Attached is a possible solution that works in all versions of Excel. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024.

    =IFERROR(INDIRECT(ADDRESS(SUM(MMULT(TRANSPOSE(N($A2=INDIRECT(B$1&"!A2:E16"))),
ROW(INDIRECT(B$1&"!A2:E16")))),SUM(MMULT(N($A2=INDIRECT(B$1&"!A2:E16")),
TRANSPOSE(COLUMN(INDIRECT(B$1&"!A2:E16")))))+1,,,B$1)),"")

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 01, 2025

Attached is a possible solution that works in all versions of Excel. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024.

=IFERROR(INDIRECT(ADDRESS(SUM(MMULT(TRANSPOSE(N($A2=INDIRECT(B$1&"!A2:E16"))),
ROW(INDIRECT(B$1&"!A2:E16")))),SUM(MMULT(N($A2=INDIRECT(B$1&"!A2:E16")),
TRANSPOSE(COLUMN(INDIRECT(B$1&"!A2:E16")))))+1,,,B$1)),"")

 

Resources