Searchable dropdown lists in Excel for a table column - this maybe impossible : )

[NoviceCoder]

Hi and thank you in advance for any help. This may not be possible in excel but it's worth a try.
I am hoping to create a column of cells with each cell working as a searchable dropdown list so that when I start typing in the active cell the dropdown list changes dynamically so that I can select one of a number defined Unique Clients - in the case from a list of clients circa 500. I have made some progress with this with help from this and other sites but I am not really there yet.
I will explain briefly the key features and what the workbook looks like presently:

*I have a worksheet named "Client List". In this column A (named "Concatenate" creates a unique ID for each Client =CONCATENATE([@Surname],"'",[@Forename])).This is an excel table and list is large and quite cumbersome to use as a simple dropdown list.

*Another work Sheet named "IN_Bank receipts" is used to log separate services provided to Clients with each row containing a date (Column A), Client name (Column B derived from Concatenate]) and Column C selected from a dropdown list of limited list of services.

As explained above the client list is cumbersome and I am trying to create a searchable dropdown list in each cell of column B on the table in worksheet "IN_Bank receipts". This is relatively easy for a single searchable cell but has been difficult for multiple cells (at least for me).


I have made some progress and the code does work when I start to type in one of the active cells in Column B. However, I have to exit the cell to update the calculations and then go back into it before the dropdown list updates.


This is what I have already:

Worksheet!Client List Column B named [workings] creates a incremental number for each field of Concatenate (concatenated names of Clients in Column A) which matches search criteria and formula**
=IF(ISNUMBER(SEARCH('IN_Bank receipts'!$B$1,[Concatenate])),MAX($B$1:B1)+1,0)
Note this formula references a fixed searchable box in cell B1 of WorkSheet named "IN_Bank receipts". This box sits just above the table to log Client Services. I have created VBA to replicate any value in any selected cell in this column B in to $B$1. By doing this any typing in the active cell is replicated to B1 and should produce a searchable dropdown list of Clients any cells of Column B of the table. The VBA code in the Worksheet looks like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Option Explicit
    If Selection.Count = 1 Then
     'Put in your actual range and the cell where you the text to be shown note B3:50 but could be B500 or infinite
        If Not Intersect(Target, Range("B3:B50")) Is Nothing Then
            Range("B1").Value = Selection.Value
        End If
    End If
End Sub

The cells in Column B have a data validation source of =ClientSearchList
The defined name Client List refers to =OFFSET(DropDownLists!Z1,,,MAX(Table1[Workings]))
where Worksheet DropDownLists contains a number of functional tables for the rest of the workbook and cell Z1 down creates the expandable range using formula
=IFERROR(INDEX(Table1[Concatenate],MATCH(ROW(DropDownLists!Y1),Table1[Workings],0)),"")
note Table1[Workings] relates to (described above**):

Worksheet Column B named workings creates a incremental number for each field of Concatenate (concatenated names of Clients) Column A which matches search criteria and formula =IF(ISNUMBER(SEARCH('IN_Bank receipts'!$B$1,[Concatenate])),MAX($B$1:B1)+1,0)


So I hope that all makes sense and I have tried to provide enough information about the workbook. If you feel as though there is much simpler approach to this task and my efforts have lead me down an increasingly complex and clunky route then i am more than happy to reconfigure. Thank you in advance.

 

[Akuini]

As an alternative to data validation I suggest you use a searchable combobox, you can find an example here:
best-way-to-create-a-searchable-drop-down-list-with-auto-complete-functionality-to-cells-in-a-column

 

[NoviceCoder]

thanks for the comments
I've run the code for combobox and I can't get it to work
I'm running Office for mac 2011 which I suspect is the problem
Combobox may be a solution an alternative to data validation but I'm not sure I have the functionality in my 2011 version?

 

[Akuini]

Ah, sorry, the combobox I'm talking about is an activex control, I don't think activex control work on Office for Mac.

 

[NoviceCoder]

great solution here:

 

[Akuini]

Glad you found a solution.