4

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?

0
2

enter image description here

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.

enter image description here

  • 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.

3
  • That's the closest thing I have ever seen to get this functionality, thanks for the answer! However, does that mean we need to create a combo box like this for each cell of the column? That seems unapplicable for a list that could grow. – Pacopaco Jun 1 at 20:55
  • Glad to help you @Pacopaco ,,, yes ,, if U are looking for AUTO Complete then need as many Combo Boxes,, if U have Multiple Column for the same peroration,,,, this is the ultimate non programming method,,, is tedious but quit useful too. And if you are expecting the LIST to add on values in future or the SOURCE list A116:A140 will grow then the DYANAMIC NAMED RANGE can be used instead of A116:A140☺ – Rajesh S Jun 2 at 7:22
  • ah, nice, the dynamic range will definitely help! – Pacopaco Jun 2 at 18:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.