Pulling table data from many sheets into one table

[Serdet]

Hi Everyone,

I have an excel workbook containing 20 sheets (named 1 through to 20). 

Each one of these sheets contains data in columns B - AB and rows 14 - 250.

I want to pull columns D & AB from every sheet into a new summary sheet if column B contains text "3m".

Is this possible? Especially without getting loads of row gaps in the data.

The formula will need to be present so when data is entered in sheets 1 - 20, it automatically pulls through to the summary sheet.

Many thanks,

Elliot  

[#2]

@Serdet 

Is this possible? 

Yes, it's possible.

Especially without getting loads of row gaps in the data.

Depends, but most likely.

Your description, however, is just clear enough to answer those questions, yet vague enough to require more information to be more specific in the answer.

It would help us help you if you would post a copy of your workbook (or a mockup if the actual contains confidential or proprietary data) on OneDrive or GoogleDrive, and then paste a link here that grants edit access to that workbook. Not an image; the actual workbook (or mockup).

[#3]

@mathetes 

Thank you for your quick response. As data is sensitive, I have created an example document and shared in the below link.

Sheets 1 - 20 are in the same format as the real document. I have also added a 'Required Output' sheet which shows how the data needs to be extracted and formatted. 

Your help would be much appreciated  

https://docs.google.com/spreadsheets/d/1n5tsI966LF2wJepWRqCjnzIMWq3N6dB1/edit?usp=sharing&ouid=10272...

[#4]

@Serdet 

If you have 365, perhaps this:

=LET(stack,VSTACK('1:20'!$A$14:$AB$20),reference,TAKE(stack,,1),FILTER(CHOOSECOLS(stack,4,28),reference="3m"))

[#5 · Accepted solution]

@Patrick2788 

Patrick, you're way ahead of me in grasping those functions. (VSTACK in particular is one I have never used; not even sure I've seen it in operation before).

It seems to me that the one thing your formula is missing, however, is @Serdet 's original request that it pick up the data from those subordinate sheets when column A (in this case) has the value "3m"

I've taken the liberty of changing a few of the values in the attached revision of your sheet so as to verify that different data are coming from different sheets as desired. But I'd like to see how the formula gets revised to pick up that "3m" criterion originally specified.

[#6]

@mathetes @Patrick2788

Thank you very much for helping me with my problem.

I have revised the formula to pick up "3m" in column B by using the below:

=LET(stack,VSTACK('1:20'!$B$14:$AB$250),FILTER(CHOOSECOLS(stack,1,3,25),TAKE(stack,,1)="3m"))

[#7]

Good catch. It doesn't affect the sample in this case but I've updated my post to include that criteria.