Log In
Register
Home
March 10, 2016
Offline
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!
See Answer
July 16, 2010
Offline
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
Answers Post
November 19, 2017
Offline
is this what you want?
1 Guest(s)
