Forum Discussion
wildcard for data validation in excel office 2016.
Nutmegg It looks like you submitted this 2x, maybe delete the other.
As for the problem, the spread sheet or similar would be helpful, but the way custom search drop downs are typically set up is to use a helper column somewhere that does the filtering for you. So you have a column that will return all the values from a list that match the value in the cell you have in the drop down and the drop down validation uses that filtered column for the list. In Excel 365 it is very easy because you can use FILTER() function to create the filtered column and then reference that filtered list using the cell reference followed by # to include the entire list. But if you need it to be backward compatible I think you will need to create that filtered column using the old functions (and additional helper columns may make it easier) and then the data validation can refer to that list.
i just did a quick search and found this reference: https://trumpexcel.com/dynamic-excel-filter/
That reference uses an actual drop down control (which maybe you could use) but it also does a nice job showing how it uses 3 helper columns to filter the data so you could use that part to create your filtered list for the data validation. So i copied their file and added those same basic helper columns to the Unique list on the second page and added another column to show the country's name (you could create more complex formulas to reduce the number of helper columns but I think it is nice to see what is happening). An you can see the cell I added that uses the data validation technique. Hope that helps.
Nutmegg It looks like you submitted this 2x, maybe delete the other.
As for the problem, the spread sheet or similar would be helpful, but the way custom search drop downs are typically set up is to use a helper column somewhere that does the filtering for you. So you have a column that will return all the values from a list that match the value in the cell you have in the drop down and the drop down validation uses that filtered column for the list. In Excel 365 it is very easy because you can use FILTER() function to create the filtered column and then reference that filtered list using the cell reference followed by # to include the entire list. But if you need it to be backward compatible I think you will need to create that filtered column using the old functions (and additional helper columns may make it easier) and then the data validation can refer to that list.
i just did a quick search and found this reference: https://trumpexcel.com/dynamic-excel-filter/
That reference uses an actual drop down control (which maybe you could use) but it also does a nice job showing how it uses 3 helper columns to filter the data so you could use that part to create your filtered list for the data validation. So i copied their file and added those same basic helper columns to the Unique list on the second page and added another column to show the country's name (you could create more complex formulas to reduce the number of helper columns but I think it is nice to see what is happening). An you can see the cell I added that uses the data validation technique. Hope that helps.
- mtarler. I have spent some time working on the dynamic filter you tube video you provided but it doesn't do what i want. Can i email you my workbook and you can have a look at it. Perhaps there is another way of getting the drop down filter to work.
- sure you can send it to me. Did you look at my attached file in the previous post? I added a drop down using data validation using helper columns but maybe I should have removed the other stuff as maybe it became confusing.
