VSTACK all ranges in Spill array

[Andrew-Mark]

Hi everyone,

I have attached a sample workbook; it is a .xlsm file as have used the GET.WORKBOOK function.
There are no macros within it but felt I should highlight it here.

In cell K2 of ws 'CPD List' I have a dynamic list of all cell ranges from the other ws I wish to VSTACK.

In cell B3 of ws 'CPD List' I have used a 3d array but this is static and do not wish to have to reset this every time a new event happens.
I would use INDIRECT but this does not work with 3d arrays.

My other thought was to use TEXTJOIN on the spill array in K2 but I cannot get this to work either.

Does anyone know a way to use VSTACK in combination with the dynamic spill array to produce the list in B3#?

[romperstomper]

You could use something like:

=DROP(REDUCE(0,K2#,LAMBDA(a,b,VSTACK(a,INDIRECT(b)))),1)

[Andrew-Mark]

That has worked a charm.
Now to take that apart and learn something new