Custom Drop Down List

Try this code. Put them on the Worksheet_SelectionChange Event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wsParts As Worksheet, wsNotes As Worksheet
Dim LR As Long, i As Long, j As Integer
Dim colList As Collection
Dim sList As String, sOrder As String

Set colList = New Collection

With ThisWorkbook
    Set wsParts = .Worksheets("Parts")
    Set wsNotes = .Worksheets("Notes")
End With

With wsParts
LR = .Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To LR
        If Not .Cells(i, 1).Value Like "*Total*" Then
            On Error Resume Next
            colList.Add .Cells(i, 1).Value, CStr(.Cells(i, 1).Value)
        End If
    Next i
    
    On Error GoTo 0
    
    For j = 1 To colList.Count
        sList = sList & "," & colList.Item(j)
    Next j
    
    sList = Right(sList, Len(sList) - 1)
    
End With


With wsNotes
    With .Range("A2").Validation
        .Delete
        .Add xlValidateList, xlValidAlertStop, xlBetween, sList
    End With
End With

With wsParts
    sOrder = ""
    
    For i = 2 To LR
        If .Cells(i, 1).Value = wsNotes.Range("A2").Value Then
            sOrder = sOrder & "," & .Cells(i, 6).Value
        End If
    Next i
    
    sOrder = Right(sOrder, Len(sOrder) - 1)
End With
    
With wsNotes.Range("B2").Validation
    .Delete
    .Add xlValidateList, xlValidAlertStop, xlBetween, sOrder
End With

End Sub