Forum Discussion
Solved
How do I create a validation dropdown that uses the FILTER function?
Hi all,
I have successfully used the INDIRECT function to create dropdown lists for a single column, such as here in cell C2, where I have a simple dropdown of four shapes.
That list was created by defining a data validation that refers to a dynamic list in a table (called shape_tbl), which is in another tab.
But I have another table where I link a each shape to several colours.
As you can see, each shape has a set of colours, and many of the colours are repeated - but each shape+colour combination is unique.
My problem occurs in the next table, where I want to add multiple attributes to each shape/colour combination.
In this table, I have 3 columns, shape, colour, and attribute. In fact, it's the table that I used for the first figure above illustrating the dropdown for shape. Let me repeat it, but this time I'll show you the dropdown for colour, which uses a cell validation list of
=INDIRECT("shape_colour_tbl[colour]")
which not only has repeats, but has colours that may not be associated with the shape - for instance, you can see from the table above that the shape Oval has only six colour entries, but in the dropdown shown below, there are way more than the six colours linked to the Oval shape.
So what I WANT to do is change the validation list to show just those six colours that are associated with the Oval shape when selecting the colour in cell B4 above. And of course, I'd expect the list to be restricted to whatever colours were linked to whatever shape. Dynamically. And without resorting to VBA, because ultimately this sheet has to be sent to customers who will not open it if there are any macros.
Now I can easily extract a list of the needed colours using
=FILTER(shape_colour_tbl[colour],shape_colour_tbl[shape]=attribute_tab!A4)
as I have done in cell F2 below:
And indeed, I could now change the validation for cell B4 to be simply
=F2#
but there is nothing dynamic about that! No point in having to custom curate every cell's validation list.
Ideally of course, I'd like to be able to put the FILTER statement in the Validation list - but I've not been able to find a way to make it work.
So my question (finally) is - does anyone know how to create a dynamically expandable dropdown based on items filtered from another list?
Link to source spreadsheet https://1drv.ms/x/s!AgTtxZayMVPJiPN90tjZaSwfl-ER0A?e=PbnUGK
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.
