create dynamic dropdown based on UNIQUE values from a column within a table

[Larry2019]

Hello expert Excel users!

Below i will describe what is the purpose of my first forum post, so bear with me.

So i have multiple tabs defined as tables in a big excel file.

Each table has one or more columns but then, some of the tables do have common fields among them.
What i want to do is for these common fields based on what i input let's say in what i would call the "parent" table, the other "child" tables to get the dynamic unique values in a dropdown to feed from.

So to be more explanatory,
Table A has columns 1 2 3. Table B has columns 4 2 5 and 6. So common ground for them is column 2. I consider table A the parent and i want column 2 in the "child" table (B) to get whatever unique values i add in the parent table same column as unique values in a dropdown.

Let me know and thank you for your support!

 

[sijpie]

Hi Larry. Part of your explanation is clear, but the second part I do not fully understand:
Sheet B has one column with the same heading as a column in Sheet A.
Now you want to have dropdown validation in sheet B on that column. The values available in the dropdown should be the unique values in the Sheet A column with that heading.

Is that correct? Is the use of macro's OK?

 

[Larry2019]

Hi sijpie,

I want in sheet B the column with the same values as A to get them based on dynamic validation list upon whatever values are contained uniquely in sheet A for the same column.
So let's say sheet A has column Driver with values John, Dan, Papa. Sheet B has among other columns also column Driver but here i want somehow to bring over the John, Dan, Papa or whatever it is contained there as a dropdown.

Whatever works would be helpful to get a start.

 

[sijpie]

OK, the solution works really well, but you need to take particular care in the following instructions to get it set uup properly:

1. Rightclick on the tab for sheet 'B' and select 'View Code'.
2. This will open the VBA editor (macro editor) on the module for this sheet.
3. Now copy / paste the following code into the editor:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim vList As Variant
    Dim rF As Range
    Dim lC As Long
    
    If Target.Cells.Count = 1 Then  'Only for single cell selection
        ' See if title appears on sheet A
        With Sheets("A").Range("A1")        '<<<<<<<<  Here the sheet name of sheet A needs _
                                             to be modified as well as the top left corner _
                                             of the range with headings on that sheet <<<<<<<<<<<
            'Find the current heading title in the headings of Sheet A
            Set rF = .Resize(1, .End(xlToRight).Column).Find(Cells(1, Target.Column))
        End With
        If Not rF Is Nothing Then
        'The heading title was found. Fill an array with the column below the heading
            vList = rF.Resize(rF.End(xlDown).Row - rF.Row, 1).Value
            'Call the sub to create the validation list, _
             passing it the current cell and the values on sheet A
            BuildValidation Target, vList
        End If
    End If
End Sub

1. Next look at the comments (in green) and find the comment starting with <<<<<
2. On this line edit the sheet name for the sheet 'A' (maybe you called it A, or A was just the example name for the master sheet)
3. Also edit the A1 cell address if the table on sheet A does not start in A1
4. Then in the VBA editor in the window top left, you can see your workbook, with all the sheets
5. Rightclick on 'ThisWorkbook' and select Insert.../Module
6. A new macro module will open.
7. Paste the following code here:

Option Explicit

Sub BuildValidation(cCell As Range, vSource As Variant)
' Build a validation list from the unique values in an array _
  passed as vSource. Add this validation list to the cell _
  passed as cCell
  
    Dim dDic As Object                    'will hold a dictionary
    Dim lR As Long, lL As Long
    Dim vItm As Variant
    Dim sValidation As String
    
    Set dDic = CreateObject("Scripting.Dictionary") 'Set up as Dictionary object
    On Error Resume Next        ' required to avoid error when trying to add _
                                  duplicate values to dictionary
    For lR = 2 To UBound(vSource, 1)
        dDic.Add vSource(lR, 1), vSource(lR, 1)   'Add each value in source _
                                 The dictionary will not accept _
                                 duplicates in the key so is filled _
                                 with unique values
    Next lR
    On Error GoTo 0             'reset error behaviour to normal
    'build the validation list in a comma delimeted string
    For Each vItm In dDic.items
        Debug.Print vItm
        sValidation = sValidation & vItm & ","
    Next vItm
    lL = Len(sValidation)
    If lL Then
        'remove trailing comma
        sValidation = Left(sValidation, lL - 1)
        'Now add to validation of the cell
        cCell.Validation.Delete
        cCell.Validation.Add xlValidateList, Formula1:=sValidation
    End If
End Sub

1. Now you need to copy thecode form the Sheet B module to sheets C, D etc. Note: do not copy it to Sheet A!!
2. Do this by (in the VBA editor) doubleclicking on Sheet B in the top left window.
3. Select the macro and copy (Ctrl-a, Ctrl-C)
4. Double click on each of the other sheetnames in the top left window and paste the code into their modules

Now you can test your workbook. Go back to Excel and click in a cell in say sheet B where you know their is no equivalent column in sheet A.
Nothing should happen
Then click on a cell in a column which has a similar column heading in Sheet A. Now a selection arrow should appear next to the cell.
Click on the arrow, and you should see the equivalent list to select from