Dependent between 2 dropdown columns

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

Attachment 21641
Attached Excel file for your reference.

Regards,
Anantha

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

Quote:

---

Originally Posted by p45cal

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

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:

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

---

Hi p45cal,

Thank you so much.

Regards,
Anantha Krishna