Best way to create a searchable drop-down list with auto-complete functionality to cells in a column?

[dougmarkham]

Hi Folks,

I have a time-sheet workbook with two worksheets (ws). In sheet 1 (the time-sheet ws), Column A is "Employee".

Time-sheet worksheet.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Start time[/TD]
[TD]Finish time[/TD]
[TD]Hours worked[/TD]
[/TR]
[TR]
[TD]Joe Blogs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





I wish to be able to start typing a different employee on each row of column A (these employees are temporary workers) and have a drop-down offer me matching results to select.
Worksheet 2 is a master list of temporary employee names on.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Employee List[/TD]
[/TR]
[TR]
[TD]Joe Blogs[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[/TR]
[TR]
[TD]Andrew Peters[/TD]
[/TR]
[TR]
[TD]Sarah Cook[/TD]
[/TR]
[TR]
[TD]Joseph Belkin[/TD]
[/TR]
</tbody>[/TABLE]










Goal:
a) I envision the user to be able to start typing an employee name,
b) ...as the user is typing, I would like excel to search the master list and offer a set of matching names (a list which shrinks as the user types).
c) I would like the user to be able to select the correct employee name and have that name populate the cell (i.e., to use the down arrow key to select the desired option and press the enter key to populate the cell).

What I've already found from searches:
*There are multiple options to create an ActiveX text box for creating a searchable drop-down; however, these don't offer any way of making every cell in a column of a table to become a searchable-drop-down.
*I've found a few methods using formulas: they tend to offer only the option to click on the drop-down arrow, rather than drop-down list as you type. I'm trying to get away from options which cause the user's hands to leave the keyboard to operate a mouse.

The end goal would be to facilitate the above for existing temporary employees; however, if the employee is a new worker, I am looking to use VBA to transfer the new name from the a cell in column A to the master list via clicking a form control button.

Would anybody be willing to help me find a viable way to do this?

Due to restrictions, I don't think I'll be able to install any add-ins: just VBA, userforms and formulas.

Kind regards,

Doug.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.

 

[emul]

Hi Akuine, Thanks for you contribution and kalagas thank you for your post.

Kalagas I hope you don't mind asking Akuine for some adjustment, which by the way doesn't have a negative impact on your request, but your post is a good basis for my intended adjustment.

Akuine,

I have a problem/suggestion i want to present to you to see if you have a solution.
Unfortunately i have little knowledge of vba, although understand your code.

I want to use your code to look up and set an accountnumber of an account scheme. The account scheme has 2 columns, one with accountnumbers and one with the descripitons of the accounts.
What i want to archieve is that the searchable dropdown list shows the content of both columns, but after press "Enter" the cell sets only the account number.

I think maybe it is possible that i create a new column filled with both the account number and the account desription, which can be used for the searchable dropdown list and use extra vba code to set the content of a cell of a variable column parallel, before or after, the column used for the searchable dropdown list.
So the cell should be filled with the content of the cell x-columns -/+ (before or after) the cell found by the searchable dropdown list.
Because of the extra column contains both values, i can search for the number as well as for the description.

Furthermore, the range in your example is static B2:B20, i want a dynamic range, for examble "b2"&":"&"B"&max(other range), or something, as mentioned i don't know the right vba code.

Is this possible?

Thanks,

Frank

 

[Akuini]

1. I think it's possible, but can you give me some example data?
2. And I don't understand this: and use extra vba code to set the content of a cell of a variable column parallel, before or after, the column used for the searchable dropdown list.

 

[emul]

Hi Akuine,

What i mean is:
Example,
sheet2 column A contains the value of both columns B and C merged.
Column B contains the account numbers and
column C contains the account descriptions

Column A of sheet2 is used in the searchable dropdown list in sheet1 in, for example, column D2: D20.
If a value has been looked up in one of cells D2 through D20, using the dropdown list and "Enter" is pressed,
then the combobox.value should not be put in the relevant cell of column D, but only the account number, which is in column B of sheet2.
The corresponding value of the account description should then appear in the cell of an other column, for example, column E of sheet1.

 

[emul]

Attached printscreens to visualize

 

[Akuini]

1. "the account numbers", do they have fixed length? say 6 characters?

2. the range in your example is static B2:B20, i want a dynamic range, for examble "b2"&":"&"B"&max(other range), or something
so what is the criteria to define the last row? say last row of col E?

 

[emul]

1. "the account numbers", do they have fixed length? say 6 characters?

2. the range in your example is static B2:B20, i want a dynamic range, for examble "b2"&":"&"B"&max(other range), or something
so what is the criteria to define the last row? say last row of col E?

1 Yes they have 4 characters, but sometimes 6, so, that’s something we can specify above the macro

2 I use a extra column, which determines wether a booking must be made. In the column it counts the rows, if there is a date and or number in a specific other column.
I thougt that if i can use te max() function, or something similar, then it is possible to make it dynamic.
Because it is Always one column, which contains numbers from one to x, we could specify the column which contains the numbers, also above the macro.

 

[Akuini]

2 I use a extra column, which determines wether a booking must be made. In the column it counts the rows, if there is a date and or number in a specific other column.
I thougt that if i can use te max() function, or something similar, then it is possible to make it dynamic.
Because it is Always one column, which contains numbers from one to x, we could specify the column which contains the numbers, also above the macro.

Sorry, I can't understand it. Could you explain in more detail using an example?
Another option:
A simpler way to use the combobox in dynamic range is to set your data in an actual table (not just a range). This way, every time the table expand the combobox will expand accordingly.
So can you put your data in an actual table?

 

[emul]

A table is not an option for me.
If there is a cell which contains the number of the last row, then is it possible to make a dynamic range by vba?
Say a want a range of B2:B40 and number 40 is a number showed in cell A3.
If I want to change the range to B2:B60, by changing the value of cell A3 to 60. Is that possible?

 

[emul]

I use Column C to count the rows.