Gather information across multiple sheets where the info may be in different cells

Hello

I need to gather information from multiple sheets and post in a cumulative sheet in the same workbook - Model & Serial #'s. However, the data is not in the same cell in each worksheet. It is in the same column but not the same row as it's listed on the bottom of an invoice and the lines on that change depending on how many lines are on the invoice. HOWEVER, the header for the data that I need is the same. Ideally there would be ANOTHER report that I could run that would show this data - unfortunately there is not. Apparently it's a work in progress. Therefore I need to create my own workaround. Is there a way to reference data based on a common header and dump it back into one cumulative sheet?

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

one way:

=LET(A,VSTACK(Sheet1:Sheet4!$A$1:$B$100),B,FILTER(A,INDEX(A,,1)<>""),INDEX(B,MATCH(1,(INDEX(B,,1)=B2)*(INDEX(B,,2)=C2),0)+2,))

copied down.

Try:

=INDEX(INDIRECT("'"&A2&"'!a2:a5000"),MATCH(D$1,INDIRECT("'"&A2&"'!a1:a4999"),0))

and:

=INDEX(INDIRECT("'"&A2&"'!b2:b5000"),MATCH(E$1,INDIRECT("'"&A2&"'!b1:b4999"),0))

I made my sample too simplistic because I cannot replicate these formulas in my workbook. So I've copied some pages and removed all the personal data to show how this sheet is set up. The columns that the model and serial number are in are not simply A & B or next to each other. They are actually in merged cells. Model is in column T and Serial Number is in Column Y.

The attachment didn't attach. Please try again.

I hate using INDIRECT, it can cause performance issues because it updates every time anything changes. But if you have LARGE numbers of sheets, mine will become impractical, too.

=IFERROR(INDEX(INDIRECT("'"&A2&"'!T:T"),MATCH(D$1,INDIRECT("'"&A2&"'!T:T"),0)+1),"")

see file.