=Sheet2!$A$2:ИНДЕКС(Sheet2!$A:$A;ПОИСКПОЗ("яяя";Sheet2!$A:$A))

Sub DropDownList()
    With Range(Sheet1.Range("S6:X6"), Sheet1.Range("S6:X6").End(xlDown)).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=list"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Sub DropDownList2()
    Dim rng As Range
    Set rng = Selection
    Sheet1.Range("S6:X6").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Sheet2!$A$2:$A$300"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    rng.Select
End Sub

Sub DropDownList()
    Set R = Sheet1.Range("S6:X6")
    With Sheet1.Range(R, R.End(xlDown)).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='Sheet2'!$A$2:$A$" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

=Sheet2!$A$2:ИНДЕКС(Sheet2!$A:$A;СЧЁТЗ(Sheet2!$A:$A))

Sub DropDownList()
    r_ = 6
    For i = 19 To 24
        r1_ = Cells(Rows.Count, i).End(xlUp).Row
        If r1_ > r_ Then r_ = r1_
    Next i
    With Range("S6:X" & r_).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=спис"
    End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tValue As Variant, rOffset As Long, cOffset As Long
    
    With Target
        If .Column = 1 Then
            tValue = .Cells(1, 1).Value
            rOffset = ActiveCell.Row - .Row
            cOffset = ActiveCell.Column - .Column
            
            With Columns(1)
                With Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).EntireRow
                    .Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlNo
                End With
                With .Find(tValue, after:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
                    Application.Goto .Cells
                    If .Row = 2 And rOffset = -1 Then rOffset = 0
                    .Offset(rOffset, cOffset).Select
                End With
            End With
        End If
    End With
    Application.EnableEvents = True
End Sub