Autocomplete list in cells to select species

[janhendrik]

Hi all

I fill in a lot of data from fieldwork sheets that contain species names. But the spelling of such names is often difficult, and it is near impossible to always spell everything correctly. So I would love an autocomplete function and maybe dropdown list for the cells in an excel column, which gives me suggestions as I start typing.

However, since the species names are composed of two parts (genus and specific epithet, for example Panthera leo), I would like the autocomplete to be able to take the first few letters of the genus name together with the first few letters of the specific epithet, and give a suggestion based on both. If I only type the first part, then all suggestions will come up which have those letters in the genus name. The website iNaturalist does this beautifully when you add species identifications to observations.

Here is an example with Panthera leo (lion) and Panthera pardus (leopard). What I would like is the following: when I start to type "pan", or even just "p", then both suggestions would appear and I could select either one. But if I type "pan par", or even just "pan p", it only gives Panthera pardus as an option, since it combines the first parts of both words to give a suggestion.

Some more examples. First a list (just a bunch of plant names):

Adromischus diabolicus
Anacampseros recurvata
Bauhinia bowkeri
Conophytum blandum
Conophytum limpidum
Crassula decumbens
Dinteranthus wilmotianus
Ectadium virgatum
Helichrysum marmarolepis

When I type "con" it would show both Conophytum blandum and Conophytum limpidum as options, but if I type "con b" it would only give Conophytum blandum as option. If I started to type "ad" it would only give Adromischus diabolicus as option.

Hope this makes sense. Also, it should not be a combo box if possible, since I need to type this into individual cells of a column.

Anyone up for the challenge? :)

[#2]

@janhendrik 

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.

[#3]

@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?

[#4]

@janhendrik 

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.