I am trying to find a way to dynamically merge several Tables in Excel.
eg. the tables are called Tbl_s1, Tbl_s2,… Tbl_sN.
These table names are stored in another Table called Tbl_Ref.
I was hoping to be able to call them all and stack them using:
=VSTACK(INDIRECT(INDEX(Tbl_Ref[Ref],,)))
This only results in a spill of ‘N’ #VALUE errors.
Does anybody have a suggestion for how I can get around this error?
>Solution :
Use REDUCE:
=DROP(REDUCE("",Tbl_Ref[Ref],LAMBDA(z,y,VSTACK(z,INDIRECT(y)))),1)
REDUCE will iterate each reference and vstack it one at a time then return the final result.
