hello,
I have a large list of cities
I gave them a name and defined a separate sheet - data validation - a list
So that I can choose from the drop-down list - the desired city.
The problem is that the list is very large and it is not possible to search for it
The question is this -
I'll set the list to ABC
The question is whether I can set the first 2 letters
Then press the arrow and it will display the cities starting with the letter I typed
Or there is another way that will not facilitate
thank you!
Hi Leah,
Thanks for sharing your sample file. This is actually a great question and I have a solution for you in the attached file, but you need to use a .xlsx file format, however your sample file is .xls, which is an Excel 2003 (or earlier) file format. Excel 2003 is no longer supported so you really should be using .xlsx file formats now.
The attached file (.xlsx) uses a Slicer to filter the data validation list by the first two initials of the city. You could shorten the list by extracting the first initial of the city and using that in the Slicer. Just change the LEFT formula in column Q of the city sample sheet.
I've written a dynamic named range formula (dnr_city_list) to get the list of cities from the PivotTable for use in the data validation list.
I hope that helps.
Mynda
is this what you want?
A useful and wise and professional answer!
Thank you Minda for the excellent response.
I'll delve into the dynamic formula and if I have a question, I'll come up here.
clark shao said
is this what you want?
Also looks great!
The question of how to do it? Is this based on VBA?
