Linked Data Validation Tables

[MeaclH]

Hi all,

I am wondering if there is a way to have 2 seperate cells with data validation that are linked somehow, so that when a value is selected in the first table, the other table value reflects the value from the table in the adjacent column. I'd like this to be done from either column, so if a user knows the name of a location but not the 3 letter code they can select that, or vice versa, if a user knows the 3 letter code but not the name they can select that, and the 2 data validation cells will always be matched up.
I am guessing it may be some kind of IF/VLOOKUP combo but have no idea.
Any ideas, I'd love to hear them.

cheers,
Hayden

DAO Almanac.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1													CODE	LOCATION NAME	LINE(s)	Terminating Location	Stabling Location	Meal Break Location	Drivers Depot	# of Stabling Roads
2		Burnley Sidings					BLS						ACF	Aircraft	Werribee		NO	NO
3													ALB	Albion	Sunbury		NO	NO
4													ALM	Alamein	Alamein	YES	NO	NO
5													ALP	Alphington	Hurstbridge		NO	NO
6		Terminating Location			YES								ALT	Altona	Werribee		NO	NO
7		Stabling Location			YES								ARM	Armadale	Frankston		NO	NO
8		Meal Break Location			YES								ASH	Ashburton	Alamein	YES	NO	NO
9		Drivers Depot			NO								ASP	Aspendale	Frankston		NO	NO
10		Stabling Roads			5								ASV	Ascot Vale	Craigieburn		NO	NO
11		Lines			Alamein, Lilydale, Belgrave								ASY	Anstey	Upfield		NO	NO
12													AUB	Auburn	Alamein, Lilydale, Belgrave		NO	NO
13													BAT	Batman	Upfield	YES	NO	NO
14													BAY	Bayswater	Belgrave	YES	YES	NO		5
15													BBH	Brighton Beach	Sandringham	YES	YES	NO		2
16													BBN	Blackburn	Lilydale, Belgrave	YES	NO	NO
17													BCV	Balaclava	Sandringham		NO	NO
18													BEG	Belgrave	Belgrave	YES	YES	NO	YES	3
19													BEL	Bell	Mernda		NO	NO
20													BEN	Bentleigh	Frankston		NO	NO
21													BEW	Berwick	East Pakenham	YES	NO	NO
22													BFD	Beaconsfield	East Pakenham		NO	NO
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
G2:H4	List	=$M$2:$M$22
B2:E4	List	=$N$2:$N$22

 

[dreid1011]

Here is the adjusted code with columns P & Q added to the message box, and not sure why I put the "Value not found" box where I did.

Sub Worksheet_Change(ByVal Target As Range)
Dim lookupval As String
Dim result As Range
Application.EnableEvents = False
On Error GoTo ExitNow
If Not Intersect(Target, Range("A2")) Is Nothing Then
    If Len(Target) = 3 Then
        lookupval = Target
        Set result = Range("M2:M22").Find(lookupval)
        If Not result Is Nothing Then
            MsgBox Range("N" & result.Row) & " " & Range("M" & result.Row) & vbCrLf & _
            "Terminating Location: " & Range("P" & result.Row) & vbCrLf & _
            "Stabling Location: " & Range("Q" & result.Row), Title:="Location Info"
        Else
            MsgBox "Invalid entry. Try again.", vbCritical, Title:="Value Not Found"
        End If
    Else
        lookupval = Target
        Set result = Range("N2:N22").Find(lookupval)
        If Not result Is Nothing Then
            MsgBox Range("N" & result.Row) & " " & Range("M" & result.Row) & vbCrLf & _
            "Terminating Location: " & Range("P" & result.Row) & vbCrLf & _
            "Stabling Location: " & Range("Q" & result.Row), Title:="Location Info"
        Else
            MsgBox "Invalid entry. Try again.", vbCritical, Title:="Value Not Found"
        End If
    End If
End If
ExitNow:
Application.EnableEvents = True
End Sub

But to add additional lines to the message box, you can replicate the bolded line:

MsgBox Range("N" & result.Row) & " " & Range("M" & result.Row) & vbCrLf & _
"Terminating Location: " & Range("P" & result.Row) & vbCrLf & _
"Stabling Location: " & Range("Q" & result.Row), Title:="Location Info"

vbCrLf adds the new line, then I added a string to name the information showing, then the column where it's found.

Make sure you edit the message box in both sections.

 

[MeaclH]

Here is the adjusted code with columns P & Q added to the message box, and not sure why I put the "Value not found" box where I did.

Sub Worksheet_Change(ByVal Target As Range)
Dim lookupval As String
Dim result As Range
Application.EnableEvents = False
On Error GoTo ExitNow
If Not Intersect(Target, Range("A2")) Is Nothing Then
    If Len(Target) = 3 Then
        lookupval = Target
        Set result = Range("M2:M22").Find(lookupval)
        If Not result Is Nothing Then
            MsgBox Range("N" & result.Row) & " " & Range("M" & result.Row) & vbCrLf & _
            "Terminating Location: " & Range("P" & result.Row) & vbCrLf & _
            "Stabling Location: " & Range("Q" & result.Row), Title:="Location Info"
        Else
            MsgBox "Invalid entry. Try again.", vbCritical, Title:="Value Not Found"
        End If
    Else
        lookupval = Target
        Set result = Range("N2:N22").Find(lookupval)
        If Not result Is Nothing Then
            MsgBox Range("N" & result.Row) & " " & Range("M" & result.Row) & vbCrLf & _
            "Terminating Location: " & Range("P" & result.Row) & vbCrLf & _
            "Stabling Location: " & Range("Q" & result.Row), Title:="Location Info"
        Else
            MsgBox "Invalid entry. Try again.", vbCritical, Title:="Value Not Found"
        End If
    End If
End If
ExitNow:
Application.EnableEvents = True
End Sub

But to add additional lines to the message box, you can replicate the bolded line:

MsgBox Range("N" & result.Row) & " " & Range("M" & result.Row) & vbCrLf & _
"Terminating Location: " & Range("P" & result.Row) & vbCrLf & _
"Stabling Location: " & Range("Q" & result.Row), Title:="Location Info"

vbCrLf adds the new line, then I added a string to name the information showing, then the column where it's found.

Make sure you edit the message box in both sections.

Amazing, thank you again mate. You seem to be my excel hero.
Appreciate it loads!

 

[dreid1011]

Amazing, thank you again mate. You seem to be my excel hero.
Appreciate it loads!

You're welcome, and happy to help.