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 My Online Training YouTube video). A link to the file is found here. On the Standards Categori...
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.
I think you have the columns swapped:
You could place this formula in E2 and fill to the right:
=SORT(FILTER(StdCategories[[Subcategory]:[Subcategory]],StdCategories[[Category]:[Category]]=E$2,"None"))
Patrick2788 Thank You 🙏 No matter how much I "think" I know about Excel, it manages to humble me on a regular basis...
- You're welcome!
