Connect 2 Drop Down Lists

[mibach]

My scenario:

I have 2 lists: Stain Name and Stain Number. I currently have a drop down menu from which any Stain Name can be selected in Column A, I use VLOOKUP function to automatically bring up the corresponding Stain Number in Column B.

I would like to allow more flexibility. I would like to let users select either a Stain Name in Column A or a Number in Column B. If a Name is selected in Column A, the Number would appear in column B. If a Number is selected in Column B, a Name would appear in column A.

How can accomplish?

Thanks for your help

 

[Sthrncali]

When you say you have two drop down lists... Do you mean using Data Validation? Like where every row the user can open the drop down list by clicking on the cell in that row, and once they select then ColB populates with the stain number?

Or do you have a specific address, where you change the stain name/number, and it populates the reciprocal somewhere else on the worksheet?

 

[mibach]

I see I was a bit confusing, let me try again:

I have a drop down list using data validation in Column A. Once a Stain Name is selected via the drop down list in Column A, Column B populates with the Stain Number.

 

[Sthrncali]

It sounds like you want Data Validation in Both Columns, and the alternate Column would autoVlookup the appropriate data regardless of which column the user selected a value in?

If so, then the below code should work for you. You will just need to modify the ranges so they are appropriate for your workbook. Also, there is no error correction in this, as since you are using Data Validation to restrict the possible inputs, I am assuming there will be a match in your Dtable for every possible input. The code will not fill out the Col or notify the user if there is no match..

Also this needs to go in the Worksheet code container. To enter, just right click on the sheet name and click "view code".

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

    Dim Result As String
    Dim myRange As Range
    Dim Col As Integer
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target.Offset(0, 1).Value <> "" Then
    Exit Sub
    End If
    
    Set myRange = Sheet2.Range("A1:D25")  'Set this to your data table that houses the information for your Vlookup.
    Col = 2  'This is the Col you want to return from your Vlookup.
       
    Result = Application.WorksheetFunction.VLookup(Target.Value, myRange, 2, False)
    Target.Offset(0, 1).Value = Result
    End If
    
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    If Target.Offset(0, -1).Value <> "" Then
    Exit Sub
    End If
        
    Set myRange = Sheet2.Range("A1:D25")  'Set this to your data table that houses the information for your Vlookup.
    Col = 2  'This is the Col you want to return from your Vlookup.
       
    Result = Application.WorksheetFunction.VLookup(Target.Value, myRange, 2, False)
    Target.Offset(0, 1).Value = Result
    End If
    
    
End Sub

 

[mibach]

I am not very familiar with manipulating VBA code. I also don't have 2 columns of data entry. I have only 5 rows in which to enter this data. 1st row E8 contains Stain Name; H8 contains Stain Number. 2nd row E11 contains Stain Name; H11 contains Stain Number. Ect.

My ranges are named: "FinishCode" (the table with all the data)
"FinishName" (list of names)
"FinishNumber" (list of numbers)

Is there any chance you could adjust the code, or show more clearly what i must change before I can use it in my spreadsheet?

Thank you very much for your help!