Data Validation drop-down list - search column #1 and insert column #2

[nuficek]

Is there any way to create searchable data validation drop-down list for example for currencies where I can search country in column A and get currency from column B? I would like to start search and write first letters so it will automatically filter the results from column A. Then just click on the correct one and get result from column B. Something like VLOOKUP function.

example.xlsx
	A	B	C	D
1	Europe Union	EUR		USD
2	United Kingdom	GBP
3	USA	USD
4	Switzerland	CHF
Sheet2

Cell Formulas
Range	Formula
D1	D1	=VLOOKUP("USA",A1:B4,2,FALSE)

 

[Anthony47]

Something like VLOOKUP function.

That's a viable path...
-Set in Sheet2, in A1:Bxx the table Country / Currency
-Set in a cell, for example D2, a data validation based on a List; as the source of the list insert =Sheet2!$A$1:$A$xx
Now add a WorksheetChange macro:
-rightclick on the tab with the name of the sheet you are working on, select Display code; this will open the vba editor.
-copy the following code and paste into the rigth frame on the vba editor

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$2" Then             '<<<<
    Application.EnableEvents = False
    Target.Value = Application.VLookup(Target.Value, Sheets("Sheet2").Range("A1:B20"), 2, False)
    Application.EnableEvents = True
End If
End Sub

Now when you select a Country from he data validation dropdown menu, the content of the cell will revert to its currency

If you wish to restrict the list as soon you start typing (thus not using the data validation menu) you will use a combobox to be populated dynamically on combobox change; or will use the standard combobox features.

Bye

 

[nuficek]

That's a viable path...
-Set in Sheet2, in A1:Bxx the table Country / Currency
-Set in a cell, for example D2, a data validation based on a List; as the source of the list insert =Sheet2!$A$1:$A$xx
Now add a WorksheetChange macro:
-rightclick on the tab with the name of the sheet you are working on, select Display code; this will open the vba editor.
-copy the following code and paste into the rigth frame on the vba editor

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$2" Then             '<<<<
    Application.EnableEvents = False
    Target.Value = Application.VLookup(Target.Value, Sheets("Sheet2").Range("A1:B20"), 2, False)
    Application.EnableEvents = True
End If
End Sub

Now when you select a Country from he data validation dropdown menu, the content of the cell will revert to its currency

If you wish to restrict the list as soon you start typing (thus not using the data validation menu) you will use a combobox to be populated dynamically on combobox change; or will use the standard combobox features.

Bye

Hi, thanks a lot. It works like a charm.
I also tried to use Combobox to enable "search as you type" feature as you suggested but I'm getting numbers instead of currency. What am I doing wrong?

 

[Anthony47]

I also tried to use Combobox to enable "search as you type" feature as you suggested but I'm getting numbers instead of currency. What am I doing wrong?

I guess you didn't manage appropriately the combobox

If you go with the activex combobox, a simple solution is inserting a 2 columns control; then set the "ListFillRange" property to Sheet2!A1:Bxx so that both Country & Currency are visible; then set "BoundColumn" to 2, and "LinkedCell" to the destination cell. I suggest you also set MatchEntry property to MatchEntryComplete.
This way when you select a row (country & currency) you will automatically show the currency in the destination cell.

Working in this way, the Worksheet_Change macro has to be deleted; maybe you might use a Worksheet_SelectionChange to make the combobox visible only when the Currency cell is selected

HTH
Bye