Drop Down

[tbrynard01]

I use Excel 2016

Would like to know if you can create a drop down list that has two columns that will default on value

Column A Column B
Arbor Village AV

If I select Arbor Village I only want AV to appear in the cell?

Hope that makes sense help will be appreciated

 

[Kerryx]

this is one way

Book1
	A	B	C	D	E	F
1	Col A	Col B			Twn	Area
2	Arbor Village	AV			Arbor Village	AV
3					Jobs Twn	AB
4					My Twn	AC
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(A2="","",VLOOKUP(A2,E2:F4,2,FALSE))

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$E$2:$E$4

 

[tbrynard01]

this is one way

Book1
	A	B	C	D	E	F
1	Col A	Col B			Twn	Area
2	Arbor Village	AV			Arbor Village	AV
3					Jobs Twn	AB
4					My Twn	AC
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(A2="","",VLOOKUP(A2,E2:F4,2,FALSE))

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$E$2:$E$4

Thanks, I think I'm missing something as the list still puts the full name when I select it and not the abbreviated one?

 

[Kerryx]

My mistake completely misunderstood the question

test1.xlsm
	A	B	C	D
1	Select Twn		Twn	TwnSH
2	AB		Arbor Village	AV
3			Jobs Twn	AB
4			My Twn	AC
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$C$2:$C$4

Whateveer sheet you are using , click on View code insert this , adjust as needed, save and save workbook as macro-enabled workbook

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LookupTable As Range
Set LookupTable = ThisWorkbook.Worksheets("Sheet1").Range("C2:D4") ' Change here to suit your range lookup
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Application.EnableEvents = False
        Target = Application.WorksheetFunction.VLookup(Target.Value, LookupTable, 2, False)
        Application.EnableEvents = True
    End If
End Sub

 

[mikerickson]

Set the .TextColumn property of the ComboBox to 2

 

[tbrynard01]

Thank you, that did it.