2 Column Dropdown List That Must Match Dependent on Selection.

LibertyAvenger

New Member
Joined
Mar 27, 2022
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
@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.
 
Upvote 0
@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.
PNG image.png

Unfortunately I can not post this on my work computer using the mini spreadsheet link.
 
Upvote 0
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.
 
Upvote 0
Try this:
The macro is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1).
VBA Code:
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:

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.
 
Upvote 0
Solution
Try this:
The macro is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1).
VBA Code:
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:

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.
 
Upvote 0
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?
 
Upvote 0
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:

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:
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top