Forum Discussion
How do I create a validation dropdown that uses the FILTER function?
RedNectar I independent of Subodh_Tiwari_sktneer came up with a similar solution but I think mine is slightly more dynamic (i.e. you don't need to fill down). So I also create a lookup list but dynamically so it will expand with the table size. The data validation then looks at the corresponding row of that table, however, it looks at the full table width meaning shorter lists have lots of extra blanks at the end.
Please look at the sheet called "Validation" in the attached which contains a setup for the validation lists and then on the attribute_tab sheet, the first validation uses =Validation!$A2# as the source for the validation list which is then copied down. See if this helps.
Oh My!. What a task Subodh_Tiwari_sktneer and mtarler have given me. Both solutions are brilliant, and as far as the validation formula goes, I do prefer Subodh_Tiwari_sktneer's simple
=Validation!$A2#method over the rather more complicated
=OFFSET($L$1,ROW()-1,0,1,COLUMNS($L$1#))formula.
But at the end of the day, it was the sheer elegance of the
=IFERROR(REDUCE("Colour Opts",Table2[shape],LAMBDA(p,c,VSTACK(p,TOROW(FILTER(shape_colour_tbl[colour],shape_colour_tbl[shape]=c,""))))),"")formula that mtarler suggested that won me over.
A million thanks to both of you for your efforts, I wish I could mark both as "best answer" because each has its own merits.
RedNectar I independent of Subodh_Tiwari_sktneer came up with a similar solution but I think mine is slightly more dynamic (i.e. you don't need to fill down). So I also create a lookup list but dynamically so it will expand with the table size. The data validation then looks at the corresponding row of that table, however, it looks at the full table width meaning shorter lists have lots of extra blanks at the end.
