Forum Discussion
wildcard for data validation in excel office 2016.
As a back story, i created some worksheets in office 365 but found they are not backward compatible with office 2016. I have a list of 250 products on my Product_List page, and in my Quote_Form i have a drop-down list that i want to link to a product. e.g. when i enter 'square' into line 1 of my Quote_Form it currently brings up all 250 items instead of just the 2 items with 'square' as part of the description. In 365 version if i enter a # at the end of the data validation source it works, but this # doesn't work in 2016 version.
Is there some way of narrowing down the search in data validation or do i have to do it somewhere else for it to work?
I'm happy to send the workbook if someone can assist.
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.- 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.
