Forum Discussion
JoJo1
Oct 01, 2025Occasional Reader
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 ...
- 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
Oct 01, 2025Gold Contributor
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)),"")