Excel data validation with suggestions/autocomplete

Apologies for my low level of Excel understanding, maybe what I am looking to do is not possible.

I have a list of 120 entries that I want to use as data validation. But instead of people having to scroll down the 120 options it would be great if they can start typing and see the options that could match what they are willing to write. Kind of like in autocomplete, I have found this tip.

I would like to have more options when the typing starts.

For example this would be a part of the list:

Awareness  
Education  
Budget  
Budget Planning  
Enterprise Budget 

When typing "B" I would like the three last options to appear and to click one of them. I don't know if there is a way of include vlookup in here...

Ideally the perfect thing would be to have several "tags" in one cell, but I fear that is absolutely impossible with excel.

Thank you very much for any input and sorry if I haven't explained myself properly.

Solution 1:

ExtendOffice.com offers a VBA solution that worked for me in Excel 2016. Here's my description of the steps. I included additional details to make it easier. I also modified the VBA code slightly. If this doesn't work for you, retry the steps or check out the instructions on the ExtendOffice page.

1. Add data validation to a cell (or range of cells). Allow = List. Source = [the range with the values you want for the auto-complete / drop-down]. Click OK. You should now have a drop-down but with a weak auto-complete feature.

2. With a cell containing your newly added data validation, insert an ActiveX combo box (NOT a form control combo box). This is done from the Developer ribbon. If you don't have the Developer ribbon you will need to add it from the Excel options menu.

3. From the Developer tab in the Controls section, click "Design Mode". Select the combo box you just inserted. Then in the same ribbon section click "Properties". In the Properties window, change the name of the combo box to "TempCombo".

4. Press ALT + F11 to go to the Visual Basic Editor. On the left-hand side, double click the worksheet with your data validation to open the code for that sheet. Copy and paste the following code onto the sheet. NOTE: I modified the code slightly so that it works even with Option Explicit enabled at the top of the sheet.

   Option Explicit
   
   Private Sub Worksheet_SelectionChange(ByVal target As Range)
   'Update by Extendoffice: 2018/9/21
   ' Update by Chris Brackett 2018-11-30
   
   Dim xWs As Worksheet
   Set xWs = Application.ActiveSheet
   
   On Error Resume Next
   
   Dim xCombox As OLEObject
   Set xCombox = xWs.OLEObjects("TempCombo")
   
   ' Added this to auto select all text when activating the combox box.
   xCombox.SetFocus
   
   With xCombox
       .ListFillRange = vbNullString
       .LinkedCell = vbNullString
       .Visible = False
   End With
   
   
   Dim xStr As String
   Dim xArr
   
   
   If target.Validation.Type = xlValidateList Then
       ' The target cell contains Data Validation.
   
       target.Validation.InCellDropdown = False
   
   
       ' Cancel the "SelectionChange" event.
       Dim Cancel As Boolean
       Cancel = True
   
   
       xStr = target.Validation.Formula1
       xStr = Right(xStr, Len(xStr) - 1)
   
       If xStr = vbNullString Then Exit Sub
   
       With xCombox
           .Visible = True
           .Left = target.Left
           .Top = target.Top
           .Width = target.Width + 5
           .Height = target.Height + 5
           .ListFillRange = xStr
   
           If .ListFillRange = vbNullString Then
               xArr = Split(xStr, ",")
               Me.TempCombo.List = xArr
           End If
   
           .LinkedCell = target.Address
   
       End With
   
       xCombox.Activate
       Me.TempCombo.DropDown
   
   End If
   End Sub
   
   Private Sub TempCombo_KeyDown( _
                   ByVal KeyCode As MSForms.ReturnInteger, _
                   ByVal Shift As Integer)
       Select Case KeyCode
           Case 9  ' Tab key
               Application.ActiveCell.Offset(0, 1).Activate
           Case 13 ' Pause key
               Application.ActiveCell.Offset(1, 0).Activate
       End Select
   End Sub
   

5. Make sure the the "Microsoft Forms 2.0 Object Library" is referenced. In the Visual Basic Editor, go to Tools > References, check the box next to that library (if not already checked) and click OK. To verify that it worked, go to Debug > Compile VBA Project.

6. Finally, save your project and click in a cell with the data validation you added. You should see a combo box with a drop-down list of suggestions that updates with each letter you type.

Solution 2:

If you don't want to go down the VBA path, there is this trick from a previous question.

Excel 2010: how to use autocomplete in validation list

It does add some annoying bulk to the top of your sheets, and potential maintenance (should you need more options, adding names of people from a staff list, new projects etc.) but works all the same.

Solution 3:

There's a messy workaround at http://www.ozgrid.com/Excel/autocomplete-validation.htm that basically works like this:

1. Enable "Autocomplete for Cell Values" on Tools - Options > Edit;
2. Recreate the list of valid items on the cell immediately above the one with the validation criteria;
3. Hide the lines with the list of valid items.