EXCEl DROP-DOWN LISTS (DATA VALIDATION) - PROBLEM

[Dmrs92]

Hello,
i have a problem with drop-down list - my goal is to create two lists like:

I choose value in cell a2 and excel shows concrete value in cell b2. This is simple. But how to make excel to make it both way.

A2 choose "data" - display in b2 "excel"
b2 choose "execel" - display "data"

can anybody ] help me ?

I have list of materials and their codes names and i would like to have this effect.

Thank you in advance,

 

[Dmrs92]

OK, it's working now. Thank you for your help.

 

[mumps]

You are very welcome.

 

[Dmrs92]

Maybe you could help me again I notice that when i paste values to my range it happens that it returns only one value for every value in next column ?

 

[Dmrs92]

When i put
A it returns B
C returns D ect.
but when I paste
A returns B
C also returns B
It returning value for first pasted value.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("c56:c101,d56:d101")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Dim val As Range, rng As Range
    On Error GoTo errHandler
    Select Case Target.Column
        Case Is = 3
            For Each rng In Target
                Set val = Sheets("Sheet2").Range("A:A").Find(rng.Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not val Is Nothing Then
                    rng.Offset(0, 1) = val.Offset(0, 1)
                End If
            Next rng
        Case Is = 4
            For Each rng In Target
                Set val = Sheets("Sheet2").Range("B:B").Find(rng.Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not val Is Nothing Then
                    rng.Offset(0, -1) = val.Offset(0, -1)
                End If
            Next rng
    End Select
errHandler:
    Application.EnableEvents = True
End Sub