Dropdown list always shows unique values from a column based on lookup value

Name a cell on Sheet2 "IDs" located where a list of unique IDs can be stored - make sure that there is room for the list below that cell.

Then copy this code, right-click the sheet tab of "Sheet2" and select "View Code", and paste the code into the window that appears. Then save the workbook as a macro-enabled .xlsm. The code will create a list of unique ID values in the named range IDs - then you can use Data Validation / list option, setting the source to =IDs and the dropdown on those cells will have the updated list, which will be created any time you activate Sheet2.

Private Sub Worksheet_Activate()
    Dim rngC As Range
    Dim rngI As Range
    Dim rngID As Range
    Dim sht As Worksheet
    Dim i As Integer
    
    Set sht = Worksheets("Sheet1")
    Set rngI = sht.Cells.Find("Design ID")
    If rngI Is Nothing Then
        MsgBox "Sheet1 does not have a ""Design ID"" column."
        Exit Sub
    End If
    i = 0
    Set rngID = Sheet2.Range("IDs").Cells(1, 1)
    On Error Resume Next
    Sheet2.Names("IDs").Delete
    On Error GoTo 0
    
    For Each rngC In sht.Range(rngI(2), sht.Cells(Rows.Count, rngI.Column).End(xlUp))
        If Application.WorksheetFunction.CountIf(sht.Range(rngI(2), rngC), rngC) = 1 Then
            rngID.Offset(i, 0).Value = rngC.Value
            i = i + 1
        End If
    Next rngC
    Sheet2.Names.Add "IDs", rngID.Resize(i)
    Range("IDs").Sort Range("IDs"), xlAscending
End Sub