Data Validation "List" quandary

[BillSaffian]

I have a spreadsheet that tracks time assigned to a myriad of road/bridge construction projects with project numbers varying from 5 to 10 characters in length (no spaces). A project number may be associated with one or more town depending on the scope of the project. I set up a simple data validation list with all the project numbers and it has worked well. One issue is that it is difficult to remember the town(s) where the projects are located so I altered the validation list to show the project number followed by a space followed by the town name(s). Examples: 12210C Hinsdale/Brattleboro; 42440 Boscawen; 40362 Statewide Resurfacing. This has resulted in an unintended consequence. When the list was only the project number, the user could either pick from the list or type in the complete project number and hit enter. With the combined project number/town name list, activating the pull-down list and choosing an entry from it functions the same but attempting to type the entry still requires the entire entry be typed which is now many characters longer. In both cases, if you start typing in the cell, the list does not activate. Two questions: 1. Is there a way for the list to activate if the user begins typing such that the list eliminates entries not matching the characters entered (for example, typing 122 would eliminate all entries but those starting with 122 such as 12210C Hinsdale/Brattleboro and 12210D Hinsdale/Brattleboro)? 2. Is there a way for the list to show the combined project number / town name value but accept just the project number portion as a valid entry for the cell (i.e. a valid entry would be all characters until the space between the project number and town name is encountered such as 12210C or 42440)? Thanks in advance.

 

[Akuini]

2. Is there a way for the list to show the combined project number / town name value but accept just the project number portion as a valid entry for the cell (i.e. a valid entry would be all characters until the space between the project number and town name is encountered such as 12210C or 42440)?

Do you still need a solution for this?
I might have a solution using VBA. Let me know if you're interested.

 

[BillSaffian]

Yes, I would be interested. Thanks.

 

[Akuini]

Yes, I would be interested. Thanks.

Here's an example of 2 column data-validation:
Data validation (in range A2:A10) shows ID & Name in the list but only insert ID into the cell.
Helper range: $G$2:$G$11
Helper range is the result of concatenation of ID & Name separated by " : ". You can change the separator in this part of the code:

 Spr = " : " 'concatenation of ID & Name is separated by " : ", change to suit.

Just make sure the separator is not part of data in col ID & Name.
Data validation formula should refer to the helper range: =$G$2:$G$11
In data-validation, on the Error Alert tab, uncheck mark on "Show error alert after invalid data is entered".

In Sheet1 code window, put this code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo skip:

If Target.Cells.CountLarge > 1 Then Exit Sub
    If Len(Target) = 0 Then Exit Sub
    
    If Not Intersect(Target, Range("A2:A10")) Is Nothing Then 'Range("A2:A10") is range with data validation
        Dim tx As String, Spr As String, c As Range, v
            tx = Target.Value
            Spr = " : " 'concatenation of ID & Name is separated by " : ", change to suit
            If Len(tx) > 0 Then
                
                If InStr(tx, Spr) Then
                    v = Split(tx, Spr)(0)
                    Else
                    v = Target.Value
                End If
                Application.EnableEvents = False
                'Range("E2:E11") is col ID
                Set c = Range("E2:E11").Find(What:=v, LookIn:=xlValues, lookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
                    If Not c Is Nothing Then
                        Target = v
                    Else
                        MsgBox "Wrong Entry"
                        Target.ClearContents
                    End If
               Application.EnableEvents = True
               
            End If
 
    End If

Exit Sub
skip:
Application.EnableEvents = True
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub

BillSaffian - 2 column data validation.xlsm
	A	B	C	D	E	F	G
1	ID				ID	NAME	helper column
2	A001				A001	Clyde	A001 : Clyde
3	A006				A002	Emiliano	A002 : Emiliano
4	A007				A003	Rex	A003 : Rex
5					A004	Zachary	A004 : Zachary
6					A005	Roland	A005 : Roland
7					A006	Parker	A006 : Parker
8					A007	Keenan	A007 : Keenan
9					A008	Axl	A008 : Axl
10					A009	Patrick	A009 : Patrick
11					A010	Clayton	A010 : Clayton
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=E2&" : "& F2

Cells with Data Validation
Cell	Allow	Criteria
A2:A10	List	=$G$2:$G$11

The workbook:

BillSaffian - 2 column data validation

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[BillSaffian]

Thank you very much for your effort. I haven't tried your solution yet but wanted to clarify one thing. My validation list (what you call the helper range) is already created except I don't have the " : " separator which I can easily add. Therefore, I don't need the step that combines the ID and Name columns to create the helper range. If I understand what you've created correctly, the result of the choosing from the list (shown in column A) is just the ID portion (i.e. up to the " : "). This is not what I want. I always want the full combined value to be the result of the users choice. But I was hoping that instead of activating the list and scrolling to it, the user could type the ID number, hit enter and be rewarded with the combined value from the list. Currently if I do this, the ID portion is not recognized, and I get the invalid entry message.

More ideal would be to be able to manually start typing in a column A cell and have this action actuate the validation list display and have the list update as typing occurs to eliminate from the display those entries that don't have the characters being typed until there are either just a few options left on the display allowing the user to choose from the shortened list or the user continues to type until there is only one entry displayed and therefore, hitting enter selects that entry. This is what I had hoped for in question 1 of my original post.

 

[Akuini]

1. Sorry, in post #2 I quoted your second question, it meant that my reply was only to address that second question. And my understanding is you want to accept only project number as valid entry in the cell.

(i.e. a valid entry would be all characters until the space between the project number and town name is encountered such as 12210C or 42440)

So, my understanding is incorrect?

2. As for question #1, about autocomplete data validation, please check this discussion:
excel-365-drop-down-search-function-not-available

 

[BillSaffian]

Thank you. I appreciate the try.