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

[Stildawn]

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 Name

POD Name

Carrier

Contract 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:

Auckland

Shanghai

Hong Kong

London

Qingdao

Ningbo

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

Auckland

Shanghai

Hong Kong

London

Qingdao

Ningbo

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?

Thanks

 

[sijpie]

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.