Formula: list data from multiple tables in one single table

[Alecs]

Hello guys,

need your help with this one. 

I have 20 separeted sheets (Sheet1, Sheet2.....etc) where I insert some projects. Each project has one row and multiple columns for various data. First column contains the name of the person in charge. The maximum number of rows in a sheet is 175.

Is it possible to collect all of those rows from all 20 Sheets in one single Sheet? without blank rows. I've named that sheet: ALL DATA

Table structure and headers are the same across all sheets. The only difference is that ALL DATA begins from row 3 (all the other Sheets begin from row 4).

it does not matter the order in which the rows are displayed in ALL DATA sheet. They will be filtered afterwards. 

I've attached the file to make it easier for you :)

Many thanks in advance!

Alecs

[#2 · Accepted solution]

@Alecs 

Hi Alecs, there is the possibility to use the new (insider only) function VSTACK() in combination with FILTER(). The function is also available in Excel for the web.

=LET(in,VSTACK(Sheet1!B4:AY178,Sheet2!B4:AY178,Sheet3!B4:AY178),FILTER(in,INDEX(in,0,1)<>0))

To create your list with all 20 sheets you can use:

=TEXTJOIN(",",TRUE,"Sheet"&SEQUENCE(20)&"!A4:AY178")

Unfortunately, INDIRECT() does not seem to work in VSTACK(). Otherwise you could also combine them.

[#3]

Hi dscheikey,
amazing work. Thank you for your time. It is just what I was looking for.