Dependent Validation Using Arrays
October 18, 2018 - by Bill Jelen
Ever since Data Validation drop-down menus were added to Excel in 1997, people have been trying to work out a way to have the second drop-down menu change based on the selection in the first drop-down.
For example, if you choose Fruit in A2, the drop-down in A4 would offer Apple, Banana, Cherry. But if you choose Herbs from A2, the list in A4 would offer Anise, Basil, Cinnamon. There have been many solutions over the years. I've covered it at least twice in the MrExcel Podcast:
- The classic method used a lot of named ranges as shown in episode 383.
- Another method used OFFSET formulas in Episode 1606.
With the release of the new Dynamic Array formulas in Public Preview, the new FILTER function will give us another way to do Dependent Validation.
Say that this is your database of products:
Use a formula of =SORT(UNIQUE(B4:B23)) in D4 to get a unique list of the classifications. This is a brand new type of formula. One formula in D4 returns many answers that will spill into many cells. To refer to the Spiller Range, you would use =D4# instead of =D4.
