Forum Discussion
Solved
Dynamic array and spilled array - FILTER function behavior
I am attempting to create a dynamic dependent data validation list (using instructions found on the https://www.youtube.com/watch?v=pjLAnpBM9dk&t=26s YouTube video). A link to the file is found http...
If you keep your lists of categories and subcategories sorted, you can pull this off without the need to spill.
Set up named items inside the table for category and subcategory then use this in data validation:
=LET( first, XMATCH($A2, category), last, XMATCH($A2, category, , -1), r, last - first + 1, TAKE(DROP(subcategory, first - 1), r) )Data validation doesn't like dynamic arrays but will allow TAKE/DROP.
If you are willing to accept wrapped lists rather than a proper array of columns
= LET(
lists, GROUPBY(category, subcategory,
LAMBDA(v, TEXTJOIN(CHAR(10), , v)), , 0),
TRANSPOSE(lists)
)could give a reasonable effect.
To do 'a proper job' one contend with the MS 'array of arrays' error. Provided the number of columns is not too large
= LET(
distinct, TOROW(SORT(UNIQUE(category))),
body, DROP(REDUCE("", distinct,
LAMBDA(tbl, hdr, HSTACK(tbl, FILTER(subcategory, category = hdr)))), , 1),
VSTACK(distinct, IFERROR(body, ""))
)would give a 2D array solution