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 Categories TAB, there is a Table (StdCategories) with two columns for Category and Subcategory. The primary data validation list is for the Category values, and the dependent data validation is for the Subcategory values.
Starting in Cell E2, the Categories are populated horizontally using the UNIQUE and TRANSPOSE functions. Starting in Cell E2, the FILTER and SORT functions are used to populate the Subcategory values for each Category. There is some sort of error occurring in every other Column (starting in column F) where the work "None" is used as the error return value. For some reason, the formula is not matching the value in Row 2 with the associated values in the StdCategories table. I've verified this using the Evaluate Formula feature. I've tried searching for any help on this issue, but I either can't find it, or I'm not searching with the right terminology.
Is anyone familiar with this odd behavior, or have any suggestions on diagnosing the error?
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
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!
