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

Dependency.xlsx
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