Forum Discussion
Autocomplete list in cells to select species
Perhaps not exactly what you had in mind, but with some helper columns you can achieve something similar. The attached workbook has a small model with the species in column A and a helper column in B to create a short-code for the species as "first three characters, space, first character after first space", e.g. "Con b". This short-code is used to populate your dynamic drop-down list. For example, fill in "c", "co", "con", "con " or "con b" in H2 and the drop-down in I2 will only list the species who's (partial) short-code matches H2.
D1 picks-up the last short-code entered in column H and uses it to populate the source list for the dropdown in E. So, as long as you work down column H, the last short-code will fill the drop-down.
Should you want to go back and change an earlier entry, first type its "partial" short-code at the bottom in H, so that the drop-down is filled with the correct values again.
The neat thing is that your entry gets validated only upon entry. Once it's there the validation list may change without causing errors. Hopefully, I made myself clear and you'll manage to incorporate this idea in your own file. Just keep a close look on the ranges in the formulae and adjust them to your own needs.
Riny_van_Eekelenmany thanks for such a speedy response! I am going to look at the formulas in the doc you gave and see how I can incorporate it! Seems there is a restriction on the number of letters you type? I.e., in iNaturalist you can type any amount of letters for either of the two parts, or just the first (e.g. "a", "ad", "adr", "adromisc" and all the way till the end), and it lists accordingly. Can this be done with your coding?
If it can be done in iNaturalist it can might also be possible in Excel. My gut feeling tells me it would require more complex formulae and/or VBA coding if you want to be able to search on multiple (partial) words to populate the drop-down. And it would probably require more time than the 15 minutes it took me to cook-up my earlier "solution".
I guess it all depends on the number of hits you get when you type "Nnn n". If it finds 250 species, perhaps the short code needs to be "Nnn nnn". Aternatively, use wildcards in the search field. Then you can type any letter combination in the list of species. This is what I've done in version 2 of my file (attached). Would still be a lot easier than typing the whole name.
