Forum Discussion

JoJo1's avatar
JoJo1
Occasional Reader
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 a calculation that will "skip over" the one's not there.

So for example this sheet (28 October) there are animals without tags and we gave them numbers 0.1-0.3, and then below that you'll see that 24004 wasn't weighed on that day so I don't want the formula on the final sheet to bomb out.

 

If there's some way to do this process faster than what I'm doing currently - one by one = and then selecting the tab and the corresponding weight and animal number. 

 

Please and thanks so much!

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)),"")

     

1 Reply

  • 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