VSTACK results of indirect references from an array

Hi all!

I have a massive "demo data" workbook with 50+ sheets. Every sheet is essentially it's own complex array of transformations that results in columns of dynamic arrays.

The beauty of how it's set up is that every sheet has the same location of these dynamic arrays (see workbook attached). In other words if we have

3 sheets called:

• "data1",
• "data2", and
• "data3",

3 columns titled

• "column1"
• "column2"
• "column3"

And the first row of data is always 4, and "column1" is always in column B, we know that the 3 columns of data in each worksheet are "data#B4" , "data#C4", and "data#D4" (where # is replaced by {1,2,3} depending on the sheet name. Furthermore, because all of the data are dynamic arrays, by adding a "#" to the end of the reference, we can point an INDIRECT() function to the cell and immediately pull in the data.

I need to aggregate all of the data from all of the sheets into one sheet. Because of the massive amounts of data in each sheet, and the massive numbers of sheets, it's easiest to create an array with these column data references, and then use VSTACK(INDIRECT(<column>,<row>)) to pull in the data from each sheet. The problem is - because of the sheer number of references, it's annoying to have to do each reference by hand. I tried using BYROW(<range>,VSTACK(LAMDA(x,indirect(x))))) to achieve this, but it doesn't work.

Attaching an example workbook here - any thoughts would me much appreciated!!!!