2 Column Dropdown List That Must Match Dependent on Selection.

[LibertyAvenger]

Hi All,

I currently have a PO tracking sheet which requires both The vendor name and vendor id to be present on the sheet for each recorded purchase order.

What I am looking to accomplish is having users be able to make a selection from whichever column and have the corresponding data populate. So say for Example.

Row 4 Select “Vendor Name” under Vendor Name Column drop down list. This event triggers the matching vendor ID to populate in the vendor ID column.

The predicament is I need this to happen in both directions and change based on the current selection being made. I.e.

I selected the vendor name already but we’ve changed our mind. I know the vendor ID of the new selection and select vendor ID now the vendor name should change based on last input made.

Imagine Vlookup references cells like a mirror that reflects back the data set for that column. But obviously this isn’t a capable thing in excel alone which is why I believe there may be an elegant solution via VBA. I had seen something similar in this forum but I cannot find it again.

Sorry for the long post but if more information is needed I’ll do my best to respond a little more succinctly.

 

[Akuini]

@LibertyAvenger, welcome to the Forum.
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
This will make it easier to test & find a solution.

 

[LibertyAvenger]

@LibertyAvenger, welcome to the Forum.
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
This will make it easier to test & find a solution.

Unfortunately I can not post this on my work computer using the mini spreadsheet link.

 

[LibertyAvenger]

Sample Workbook Link made apologies for the misunderstanding.

 

[Akuini]

I can't access your workbook.
On your google drive, try:
Right click the file you want to share > Click Get Link > don't choose "Restricted", choose "Anyone with link" instead > Click Copy Link > now you get the link, just paste the link here.

 

[LibertyAvenger]

Link has been updated and should be able to be accessed. Link Update Sample Workbook

 

[Akuini]

Try this:
The macro is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1).

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo skip:

If Target.Cells.CountLarge > 1 Then Exit Sub
    
    
    If Not Intersect(Target, ActiveSheet.ListObjects(1).DataBodyRange.Columns(3)) Is Nothing Then 'vendor ID
        
        Application.EnableEvents = False
                Target.Offset(, 1) = toDO(3, Target.Value)
        Application.EnableEvents = True
    
    ElseIf Not Intersect(Target, ActiveSheet.ListObjects(1).DataBodyRange.Columns(4)) Is Nothing Then 'vendor name
        Application.EnableEvents = False
                Target.Offset(, -1) = toDO(4, Target.Value)
        Application.EnableEvents = True

    
    End If

Exit Sub
skip:
Application.EnableEvents = True
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub

Function toDO(a As Long, tx As String)
Dim c As Range
If a = 3 Then a = 2 Else a = 1

Set c = Sheets("VENDOR LIST").ListObjects(1).DataBodyRange.Columns(a).Find(What:=tx, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        If a = 1 Then 'vendor name
            toDO = c.Offset(, 1)
        Else          'vendor ID
            toDO = c.Offset(, -1)
        End If
    End If


End Function

The file:

LibertyAvenger - PO TRACKER WIP SAMPLE WB 1

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Note:
You should use data validation in col C:D to enter data.
How many rows of data are in VENDOR LIST? If it is hundreds or thousands then I suggest you set up a searchable-data-validation-combobox.

 

[LibertyAvenger]

Try this:
The macro is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1).

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo skip:

If Target.Cells.CountLarge > 1 Then Exit Sub
   
   
    If Not Intersect(Target, ActiveSheet.ListObjects(1).DataBodyRange.Columns(3)) Is Nothing Then 'vendor ID
       
        Application.EnableEvents = False
                Target.Offset(, 1) = toDO(3, Target.Value)
        Application.EnableEvents = True
   
    ElseIf Not Intersect(Target, ActiveSheet.ListObjects(1).DataBodyRange.Columns(4)) Is Nothing Then 'vendor name
        Application.EnableEvents = False
                Target.Offset(, -1) = toDO(4, Target.Value)
        Application.EnableEvents = True

   
    End If

Exit Sub
skip:
Application.EnableEvents = True
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub

Function toDO(a As Long, tx As String)
Dim c As Range
If a = 3 Then a = 2 Else a = 1

Set c = Sheets("VENDOR LIST").ListObjects(1).DataBodyRange.Columns(a).Find(What:=tx, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        If a = 1 Then 'vendor name
            toDO = c.Offset(, 1)
        Else          'vendor ID
            toDO = c.Offset(, -1)
        End If
    End If


End Function

The file:

LibertyAvenger - PO TRACKER WIP SAMPLE WB 1

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Note:
You should use data validation in col C:D to enter data.
How many rows of data are in VENDOR LIST? If it is hundreds or thousands then I suggest you set up a searchable-data-validation-combobox.

There’s over a Hundred Vendors, but Less than a thousand. It can be expanded as well which I’m not sure would complicate things.

 

[LibertyAvenger]

This works perfectly as tested. Would you be so kind as to explain the code and what it is doing and how it is achieved?

 

[Akuini]

There’s over a Hundred Vendors, but Less than a thousand. It can be expanded as well which I’m not sure would complicate things.

You can try using a free Excel add-in called "Search deList". Here:

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com

This works perfectly as tested. Would you be so kind as to explain the code and what it is doing and how it is achieved?

Which part do you need an explanation?
If you're not familiar with "Worksheet Change Event" you can read this article:

VBA Worksheet Change Events — Excel Dashboards VBA

Automatically run an excel macro when a cell changes with Excel vba. Run a macro on double click. Run a macro before close

www.thesmallman.com