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?
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? Read More
