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,

 

[oldbrewer]

material3 concrete

you want to type in material3 and see concrete appear in the next cell
AND
you want to type concrete into the right hand cell and see material3 appear in the cell to the left

you cannot do this without vba

but you can use a lookup table - see post in 5 minutes time

 

[oldbrewer]

[TABLE="width: 960"]
<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]material3[/TD]
[TD="width: 64"]concrete[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]the table below is named mytable[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]concrete[/TD]
[TD]material3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]material3[/TD]
[TD]concrete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]if you put concrete into A1 you will get material3 in B1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]formula in B1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]=VLOOKUP(A1,mytable,2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Dmrs92]

Yes i know i can do this like this, but i would like to have effect(both way) as i specify in the beginning.can you write such vba/macro ? I can't find it anywhere and i really need it.

 

[mumps]

On Sheet2 make 2 lists. In column A create a list of all the values that you want in the drop down in A2. In column B create a list of the values you want in B2 making sure that the value in column A corresponds to the value you want returned in column B. Then copy and paste the macro below into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in A2 or B2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2,B2")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Dim val As Range
    Select Case Target.Column
        Case Is = 1
            Set val = Sheets("Sheet2").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not val Is Nothing Then
                Target.Offset(0, 1) = val.Offset(0, 1)
            End If
        Case Is = 2
            Set val = Sheets("Sheet2").Range("B:B").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not val Is Nothing Then
                Target.Offset(0, -1) = val.Offset(0, -1)
            End If
    End Select
    Application.EnableEvents = True
End Sub

 

[Dmrs92]

I tried i change it to fit it in my sheet but it didn't work. My code:

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
Select Case Target.Column
Case Is = 1
Set val = Sheet("Arkusz pomocniczy do funkcji").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not val Is Nothing Then
Target.Offset(0, 1) = val.Offset(0, 1)
End If
Case Is = 2
Set val = Sheet("Arkusz pomocniczy do funkcji").Range("B:B").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not val Is Nothing Then
Target.Offset(0, -1) = val.Offset(0, -1)
End If
End Select
Application.EnableEvents = True
End Sub

The Private Sub Worksheet_Change(ByVal Target As Range) goes yellow and nothing help. Any clues ?

 

[Dmrs92]

it's prpably the intersect ou need to strictly define the cells where you make selection ? Am I right ?

 

[Dmrs92]

Sub or function not defined and second information is this comannd will stop debbuger

 

[mumps]

Try this version. The target columns have changed.

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
    Select Case Target.Column
        Case Is = 3
            Set val = Sheet("Arkusz pomocniczy do funkcji").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not val Is Nothing Then
                    Target.Offset(0, 1) = val.Offset(0, 1)
                End If
        Case Is = 4
            Set val = Sheet("Arkusz pomocniczy do funkcji").Range("B:B").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not val Is Nothing Then
                Target.Offset(0, -1) = val.Offset(0, -1)
            End If
    End Select
    Application.EnableEvents = True
End Sub

Also, please use code tags when posting code. Highlight your code and the click the # sign in the menu.

 

[Dmrs92]

Sorry for late reply, but still formula don't giving any score any ideas ?