Autofill based on a list of cells, but not the ActiveX Combobox way as its clunky


Board Regular
Aug 26, 2012
Hi All

I have a spreadsheet, that I'll be using as a template file to push data into a system.

In these spreadsheets there are a bunch of columns as below:

POL NamePOD NameCarrierContract Type

Now when a user is entering data below these, I want an autofill to happen that shows up the valid entries from a list as they type them, data validation drop down list does not do this.

For example. under Column A = POL Name

I want the user to be only able to enter from this list of Cities:

Hong Kong

So when they start typing "Ho" for exmaple, Hong Kong will autofill and they can click enter and carry on to the next column or row etc.

I found this online: How to autocomplete when typing in Excel drop down list?

This method uses a activex combobox which snaps to a current data validation drop down list and allows autofill, I dont want to use this as its clunky as hell and the users will complain, for example you cant push an arrow key to move out of the cell etc, and it makes the spreadsheet overall very laggy.

So I'm hoping there is a more elegant solution, one simple way I have is doing something like this:

POL Name
Hong Kong
Above Hidden / User starts entering data here

So in the example above I have rows 2 to 7 hidden (therefore hiding my list), then data validation>List>NOT drop down on rows 8 to infinity.

If you do this and users only enter from row 8 downwards (not missing a row, but they wouldnt anyway) then the cells will autofill based on the hidden list above and data validation will make sure they can only enter those cities etc.

But I think there must be a better way and the above solution has problems:

1) Users can unhide and mess with the list
2) Admin users will need to be able to add cities to the list and the process to set it up to work although easy is prone to mistakes etc
3) Dynamically extending the list in data validation?

So does anyone know of a better way of doing this?


Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).


Well-known Member
Nov 1, 2008
No, not me. Unfortunately the worksheet module does not have an event for changes in a cell, only a check for changes once the Enter hes been pressed or the cell is exited. In a userform this would be easy peasy.

Your idea of having a few hidden master rows above the table could be made workable using some macros for the admin: The choices could be held on a separate admin sheet one column for each list. Then with a press of the button the lists would be sorted and the relevant masterrows/columns in the usersheet updated. So the admins would not have to deal with any of that. Just updating a list.

Watch MrExcel Video

Forum statistics

Latest member