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

Stildawn

Board Regular
Joined
Aug 26, 2012
Messages
188
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:

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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,705
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

Threads
1,114,276
Messages
5,546,928
Members
410,763
Latest member
TSVIVI
Top