How can I add autocomplete to an Excel drop down validation

I have an excel column where a drop down validation is performed on a list on a separate sheet. I would like to allow the user to start typing in the cell and have the names on the reference list begin to fill in cell. For example, on the list I am validating against there may be a Smith, John, Scythe, Jane, and Mary, Typhoid. Instead of tapping the drop down, I want to be able to type "S", have both of the "S" last names appear in the drop down, type "m" and have Smith, John be the only option left. If invalid data is entered, nothing should save (or some other default behavior similar to validation should occur). I am trying to avoid using the drop down selectors because the users are on tablets and find the drop down cumbersome

Is this possible, and if so how?

1 Answer

How it works:

• From Developer Tab click Design Mode, left to it is Insert then from ActiceX Controls section, Select Combo Box and draw anywhere in blank area of you Sheet.
• Select the ComboBox and Right Click to get the Properties dialogue.
• Scroll down to select, 1-fmMatchEntryComplete in the MatchEntry Field
• Then reach to Linked Cell and Fill Cell address where you want to fill data, in shown example is C116.
• Next is fill Source Data Range in List Fill Range, here is $A$116:$A$140.
• Save the Sheet.

• Now, Click Drop Down List (Combo Box), then type the first letter into the box, the corresponding word will be completed automatically.

See the First Screen Shot above.

Adjust Cell references for Source Data in A116:A140 as needed.