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]

This is likely going to require some VBA as formulas cannot affect other cells, and I don't believe Data Validation can do that either. You could use a LOOKUP to pull one piece of data based off the other, but not both at the same time unless you used more than the two cells.

 

[MeaclH]

This is likely going to require some VBA as formulas cannot affect other cells, and I don't believe Data Validation can do that either. You could use a LOOKUP to pull one piece of data based off the other, but not both at the same time unless you used more than the two cells.

Thanks mate. I thought it may be pushing the boundaries.
What about an input box that lets you search by either location or 3 letter code and then presents the information from a LOOKUP?

 

[dreid1011]

That is doable, but is also VBA. And would the information be displayed in a popup message box, or displayed on the sheet?

 

[Eric W]

Why not use a Combo-box? For example:

You can put the combobox in A1:E1 as shown. If you use the dropdown, it looks like this:

Meaning that the user can see code and location. Once the user selects a line, the code goes into the G2 cell, and the location name goes into B2 via a lookup, and the E6:E11 values are looked up the same way. Let me know if this will work for you, and I can give you some instructions. It's easy, it'll take less than 5 minutes.

Here's the xl2bb extract - I used screen prints before since it doesn't show the control.

Book1
	A	B	C	D	E	F	G	H					M	N	O	P	Q	R	S	T
1	Select Code												CODE	LOCATION NAME	LINE(s)	Terminating Location	Stabling Location	Meal Break Location	Drivers Depot	# of Stabling Roads
2		Auburn					AUB						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											ALT	Altona	Werribee		NO	NO
7		Stabling Location			NO								ARM	Armadale	Frankston		NO	NO
8		Meal Break Location			NO								ASH	Ashburton	Alamein	YES	NO	NO
9		Drivers Depot											ASP	Aspendale	Frankston		NO	NO
10		Stabling Roads											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

Cell Formulas
Range	Formula
B2	B2	=VLOOKUP(G2,$M$2:$T$22,2,0)
E6	E6	=VLOOKUP($G$2,$M$2:$T$22,4,0)&""
E7	E7	=VLOOKUP($G$2,$M$2:$T$22,5,0)&""
E8	E8	=VLOOKUP($G$2,$M$2:$T$22,6,0)&""
E9	E9	=VLOOKUP($G$2,$M$2:$T$22,7,0)&""
E10	E10	=VLOOKUP($G$2,$M$2:$T$22,8,0)&""
E11	E11	=VLOOKUP($G$2,$M$2:$T$22,3,0)&""

 

[MeaclH]

I reckon

Why not use a Combo-box? For example:

View attachment 116796

You can put the combobox in A1:E1 as shown. If you use the dropdown, it looks like this:

View attachment 116797

Meaning that the user can see code and location. Once the user selects a line, the code goes into the G2 cell, and the location name goes into B2 via a lookup, and the E6:E11 values are looked up the same way. Let me know if this will work for you, and I can give you some instructions. It's easy, it'll take less than 5 minutes.

Here's the xl2bb extract - I used screen prints before since it doesn't show the control.

Book1
	A	B	C	D	E	F	G	H					M	N	O	P	Q	R	S	T
1	Select Code												CODE	LOCATION NAME	LINE(s)	Terminating Location	Stabling Location	Meal Break Location	Drivers Depot	# of Stabling Roads
2		Auburn					AUB						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											ALT	Altona	Werribee		NO	NO
7		Stabling Location			NO								ARM	Armadale	Frankston		NO	NO
8		Meal Break Location			NO								ASH	Ashburton	Alamein	YES	NO	NO
9		Drivers Depot											ASP	Aspendale	Frankston		NO	NO
10		Stabling Roads											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

Cell Formulas
Range	Formula
B2	B2	=VLOOKUP(G2,$M$2:$T$22,2,0)
E6	E6	=VLOOKUP($G$2,$M$2:$T$22,4,0)&""
E7	E7	=VLOOKUP($G$2,$M$2:$T$22,5,0)&""
E8	E8	=VLOOKUP($G$2,$M$2:$T$22,6,0)&""
E9	E9	=VLOOKUP($G$2,$M$2:$T$22,7,0)&""
E10	E10	=VLOOKUP($G$2,$M$2:$T$22,8,0)&""
E11	E11	=VLOOKUP($G$2,$M$2:$T$22,3,0)&""

Hi Eric, I think this would work really nicely. If you can give me some advice on how to get the combo box set up that would be wicked.
Thanks heaps,
Hayden

 

[MeaclH]

That is doable, but is also VBA. And would the information be displayed in a popup message box, or displayed on the sheet?

Hi mate,
I also think this could be an interesting way of presenting the infomation. Would be cool to populate the message box with the data from the table when either location or 3 letter code is input.
Perhaps you could share a sample?

Cheers,
Hayden

 

[Eric W]

OK, start with a COPY of your worksheet as you have it in your first post. Then click on the Developer tab. The Developer tab is not visible by default. If you haven't used it yet, click on File > Options > Customize Ribbon > then in the right box, scroll down until you see Developer, then put a check next to it and click OK.

Once on the Developer tab, click on Insert, and under the Active-X controls, find the Combo box icon (1st row, 2nd column), click on it, then use the mouse to "paint" the control on your worksheet where you want it. Then you can use the handles on the corners to move or resize it later. Right click on it, and select Properties. You should see this:

Change the column count to 2, and put the LinkedCell, and ListFillRange as shown to match your sheet. I'd also recommend changing the ColumnHeads to True, and maybe try putting 40;80 in the ColumnWidths section. You can play with the rest if you want to customize the control some more, but that's all you really need. Then go back to the Ribbon and toggle off the Design Mode button. Then insert the VLOOKUP functions from my previous post, and you should be good to go.

Let us know how this worked for you.

 

[dreid1011]

Hi mate,
I also think this could be an interesting way of presenting the infomation. Would be cool to populate the message box with the data from the table when either location or 3 letter code is input.
Perhaps you could share a sample?

Cheers,
Hayden

With your sample data provided, this works with values entered into cell A2, and looks up the values in col M or N depending on the length of the value entered. Could a location name be less than 4 characters in length?
This will show the results in a message box, and if an invalid entry is entered, another message box will show informing the user the value was not found.

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

Right click the sheet tab and select View Code. Paste the above code in the module that opens.

 

[MeaclH]

With your sample data provided, this works with values entered into cell A2, and looks up the values in col M or N depending on the length of the value entered. Could a location name be less than 4 characters in length?
This will show the results in a message box, and if an invalid entry is entered, another message box will show informing the user the value was not found.

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

Right click the sheet tab and select View Code. Paste the above code in the module that opens.

G'day again mate,
You are a great help as always. I got around to testing this and it works great. How would I go about extending the MsgBox that appeard with even more information from the data table.
Ideally I would have the information labels and corresponding results on seperate lines in the msg box.
For example,
Terminating location NO
Stabling location NO
Meal break location NO

so on and so forth.

Thanks, Hayden