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 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.