Forum Discussion
MicrosoftBuild a dynamic list based on other cell value
Hi,
I have a list of 5000 records with names of cities (list 1). The user needs to select one but given the size of the list it is difficult to find it. Is it possible to build something that creates a limited list if the user write 3 letters from the city on other cell? For example the list contains these values:
| Ārba Minch’, Ethiopia |
| Arbatache, Boumerdes, Algeria |
| Arbroath, Scotland, United Kingdom |
| Arbutus, Maryland, United States |
If in the cell A1 the user insert 'ARB' I'd like the list to display only the four values above. is that possible?
Thanks,
Ezequiel
"Legacy" variant
=IFERROR(INDEX(cities[City],AGGREGATE(15,6,1/(LEFT(cities[City],LEN($C$2))=$C$2)*(ROW(cities[City])-ROW(cities[[#Headers],[City]])),ROW()-ROW($E$4))),"")(regular formula, just drag it down)
I'd modify the Filter as
=FILTER(cities[City],LEFT(cities[City],LEN($C$2))=$C$2,"No match")
7 Replies
It is possible. Depending on the layout of your workbook/spreadsheet you might want to do this in a very open area, since the number of rows that could be returned could vary a lot.
The FILTER function would work to accomplish what you're asking. If you can without disclosing any confidential information) upload a copy -- or a sample -- of your data, I (or somebody else) can surely create an example.
ezequiel2235mathetes Thanks for the reply. I'm attaching a sample of the data. Now the list on C5 is the complete list of cities. I'd need to have the list only displaying those cities that contains the string inserted on the cell C2. I'd appreciatte the support
Ezequiel
Here it is. FILTER worked (although I had to disable the data validation you'd put in that cell for some reason!!)
I've only recently learned how to use FILTER, and it's one really neat function. I'll be interested in your reaction after you try it with all 500 records. Right now it's set to only work with three character search functions. You could modify it with some sexy additional functions so that you could use variable length search strings, but let's start simple.
