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

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
224
Office Version
  1. 365
Platform
  1. Windows
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.
EmployeeStart timeFinish timeHours worked
Joe Blogs
Jane Doe

<tbody>
</tbody>





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.
Employee List
Joe Blogs
Jane Doe
Andrew Peters
Sarah Cook
Joseph Belkin

<tbody>
</tbody>










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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
56
Hi, bobby786

How about this:
In the combobox, press ENTER to insert an entry (to the cell) then you can use CTRL+Arrow Keys to go to adjacent cell.


Let me know if you need help to implement this on your workbook.

You are superman !
can you help me implement this on my workbook please ? Is it possible without "CTRL+ " just curious for my knowledge purpose only. Btw Pressing Just enter does not shift the cell focus to below , ones need to use CTRL+Arrow keys to get out from that combobox?
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,789
Office Version
  1. 365
Platform
  1. Windows
can you help me implement this on my workbook please ?
You set the combobox on column C,E,G and the list source are in column F,C,B in sheet LISTS.
But I can see that the combobox in col G has 2 item only in the list i.e "Pending" & "Done". So I think it'd be better if you use a simple data validation in that columns. What do you think?

Is it possible without "CTRL+ " just curious for my knowledge purpose only.
Yes, but in the combobox the down & up arrow is used for selecting the item in the list, so that's not a good idea.

Btw Pressing Just enter does not shift the cell focus to below , ones need to use CTRL+Arrow keys to get out from that combobox?
Yes,
Do you want the focus to shift to the cell below after pressing ENTER?
 

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
56
@Akuini
can you help me implement this on my workbook please ?
You set the combobox on column C,E,G and the list source are in column F,C,B in sheet LISTS.
But I can see that the combobox in col G has 2 item only in the list i.e "Pending" & "Done". So I think it'd be better if you use a simple data validation in that columns. What do you think?

- Yes sure i am okay with data validation.

Is it possible without "CTRL+ " just curious for my knowledge purpose only.
Yes, but in the combobox the down & up arrow is used for selecting the item in the list, so that's not a good idea.
-
how about assign LEFT and RIGHT keys for move the cursor and UP and DOWN for move the items within ComboBox?

Btw Pressing Just enter does not shift the cell focus to below , ones need to use CTRL+Arrow keys to get out from that combobox?
Yes,
Do you want the focus to shift to the cell below after pressing ENTER?

- Yes please if you can.
 

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
56

ADVERTISEMENT

@Akuini

Link of my sample worksheet updated.

I need more help from you in solve the below :

Col S to Z of sheet CENTRAL "Material Used" , once i have a product and its date data filled from COL C to Q , then i need to assign which extra material this product will consume , So i will assign the material which has list in LIST sheet in COL G i have setup combBox here using the same technique i learnt as your source sheet.

My Goal :
-As soon i assign a material to related product ,it should auto populate in Materials Sheet which i created.
- The related product name and invoice number in sheet CENTRAL should be populated in Materials sheet , which will show that this material will be used in that product of corresponding number of invoice. ( I have made one entry manually just to make it more easy to understand that how the data will be populated , kindly check that)
- Once the material is populated all i have to do is assign the Producer name of that material and their respective dates.
- In sheet CENTAL COL S to Z contain Material Name and Status , I want it should auto update the status based on what Material Sheet Status.


I am not sure is it possible in excel world or not. Only you can answer it. :)

Regards
Bobby
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,789
Office Version
  1. 365
Platform
  1. Windows
It isn't clear to me, in which columns you want to have the combobox? C,E,S? or C,S?
 

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
56
@Akuini
Currently i want combobox in Col C , but your code is written so beautifully that i can assign any col as source and destination. By editing part of your code as you can see it below.

VBA Code:
'range where you want to use the combobox
'"Originally"Private Const xCell As String = "C3:C20000,E3:E20000,F3:F20000,G3:G20000"
Private Const xCell As String = "C3:C20000,S3:S20000"
VBA Code:
'ADJUST THE CODE IN THIS PART:
'you can have different number of columns
ary = Split("C,E,F,G,S", ",") ' columns where the combobox is located
arz = Split("F,C,A,B,G", ",") ' columns where the list as the source of the combobox is located

I have no knowledge of VBA , but i am trying to learn it by means of search and try to implement it on your supplied code. My main goal i tried to explain it in Post # 104 & 105 , please let me know if you need more explanation.

Regards
Bobby
 

Watch MrExcel Video

Forum statistics

Threads
1,118,790
Messages
5,574,308
Members
412,586
Latest member
Medhum
Top