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

Larry2019

New Member
Joined
Jun 26, 2019
Messages
2
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!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,573
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

New Member
Joined
Jun 26, 2019
Messages
2
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,573
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:
Code:
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:
Code:
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
 

Forum statistics

Threads
1,089,421
Messages
5,408,130
Members
403,186
Latest member
123hpeinstall

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top