Dependent between 2 dropdown columns

[krishak77]

Hi All,

Can any body provide solution for the below requirement.

In excel i have 2 dropdown column, ColumnA - State, ColumnB - Language, If i Select Karnataka in State , Language should be select Kannada




State
Language


Karnataka
Kannada


Tamil Nadu
Tamil


Andra
Telugu


Delhi
Hindi


Kerala
Malayali


21641
Attached Excel file for your reference.

Regards,
Anantha

[p45cal]

Do you need a drop down in column B?
in B2:
=VLOOKUP(A2,Sheet2!$A$1:$B$5,2,FALSE)

[krishak77]

Do you need a drop down in column B?
in B2:
=VLOOKUP(A2,Sheet2!$A$1:$B$5,2,FALSE)

Hello p45cal,

Thanks for your response, i dont want to keep formula in the cell, i want to select from the dropdown even in Column B.

Regards,
Anantha

[p45cal]

See code in Sheet1's code-module in the attached.
It will work on the entire column A/B of Sheet1, whether or not there's a dropdown in either column A or B, but only if it detects one of the countries in the table.

This is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ColumnACells As Range
Set ColumnACells = Intersect(Columns(1), Target)
If Not ColumnACells Is Nothing Then
For Each cll In ColumnACells.Cells
zzz = Evaluate("VLOOKUP(""" & cll.Value & """,Sheet2!$A$1:$B$5,2,FALSE)")
If Not IsError(zzz) Then cll.Offset(, 1) = zzz
Next cll
End If
End Sub

[krishak77]

Hi p45cal,

Thank you so much.

Regards,
Anantha Krishna