Nov 06 2022 03:19 AM
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
Nov 06 2022 05:24 AM - edited Nov 06 2022 05:27 AM
SolutionHi 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.
Nov 06 2022 05:38 AM
Nov 06 2022 05:24 AM - edited Nov 06 2022 05:27 AM
SolutionHi 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.