I am building a workbook that will build a BOM off of several different selection variables (21) from the user. the user output is a list of table names that correspond to the tables i want to vstack().
this is what i have so far.
The aim is for this workbook to contain no macros. i have the table list through a power query, its the only thing i know how to do with power queries.
=VSTACK(IFERROR(INDIRECT(J6),""),IFERROR(INDIRECT(J7),""),IFERROR(INDIRECT(J8),""),IFERROR(INDIRECT(J9),""),IFERROR(INDIRECT(J10),""),IFERROR(INDIRECT(J11),""),IFERROR(INDIRECT(J12),""),IFERROR(INDIRECT(J13),""),IFERROR(INDIRECT(J14),""),IFERROR(INDIRECT(J15),""),IFERROR(INDIRECT(J16),""),IFERROR(INDIRECT(J17),""))
it works very well but i would like for it to be something like this instead, for quicker later updates.
Neither of these versions work.
=VSTACK(INDIRECT(J6#))
or
=VSTACK("indirect("&J6#&")")
or
=VSTACK(INDEX("indirect("&J6#&")","indirect("&J6#&")"))
J6# is a filtered array output
=FILTER(FILTER(List_Tables[Name],List_Tables[Name]<>"Skip"),IF(ISNUMBER(SEARCH("Qty",FILTER(List_Tables[Name],List_Tables[Name]<>"Skip"))),FALSE,TRUE))
this is what i have so far.
The aim is for this workbook to contain no macros. i have the table list through a power query, its the only thing i know how to do with power queries.
=VSTACK(IFERROR(INDIRECT(J6),""),IFERROR(INDIRECT(J7),""),IFERROR(INDIRECT(J8),""),IFERROR(INDIRECT(J9),""),IFERROR(INDIRECT(J10),""),IFERROR(INDIRECT(J11),""),IFERROR(INDIRECT(J12),""),IFERROR(INDIRECT(J13),""),IFERROR(INDIRECT(J14),""),IFERROR(INDIRECT(J15),""),IFERROR(INDIRECT(J16),""),IFERROR(INDIRECT(J17),""))
it works very well but i would like for it to be something like this instead, for quicker later updates.
Neither of these versions work.
=VSTACK(INDIRECT(J6#))
or
=VSTACK("indirect("&J6#&")")
or
=VSTACK(INDEX("indirect("&J6#&")","indirect("&J6#&")"))
J6# is a filtered array output
=FILTER(FILTER(List_Tables[Name],List_Tables[Name]<>"Skip"),IF(ISNUMBER(SEARCH("Qty",FILTER(List_Tables[Name],List_Tables[Name]<>"Skip"))),FALSE,TRUE))