Forum Discussion
Solved
Formula: list data from multiple tables in one single table
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 co...
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.
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.
- Hi dscheikey,
amazing work. Thank you for your time. It is just what I was looking for.
