Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim d_ As Range, d0_ As Range
    Set d_ = Target(1)
    Set d0_ = Range("ДеталиЛитье[[ТПА1]:[ТПА5]]")
    If Not Intersect(d_, d0_) Is Nothing Then
        r_ = d_.Row
        Set slov = CreateObject("Scripting.Dictionary")
        With slov
            For Each dd_ In Intersect(Rows(r_), d0_)
                If dd_ <> Empty Then
                    If dd_ <> d_ Then
                        aaa = .Item(dd_.Value)
                    End If
                End If
            Next dd_
            ar = Sheets("ТПА").Range("ТПА[[№ ТПА]:[Категория]]")
            k_ = Intersect(Rows(r_), Range("ДеталиЛитье[Категория]"))
            raz_ = ","
            For i = 1 To UBound(ar)
                If ar(i, 3) = k_ Then
                    If Not .Exists(ar(i, 1)) Then
                        t_ = t_ & raz_ & ar(i, 1)
                    End If
                End If
            Next i
        End With
        If t_ <> "" Then
            t_ = Mid(t_, Len(raz_) + 1)
            d0_.Validation.Delete
            d_.Validation.Add Type:=xlValidateList, Formula1:=t_
        End If
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("E:i")) Is Nothing Then Application.Calculate
End Sub

=IFERROR(INDEX(ТПА[№ ТПА];SMALL(IF((ТПА[Категория]=INDEX(ДЕТАЛИ_ЛИТЬЕ!D:D;ROW(INDIRECT(CELL("address")))))*ISERROR(MATCH(ТПА[№ ТПА];INDEX(ДЕТАЛИ_ЛИТЬЕ!E:I;ROW(INDIRECT(CELL("address"))););0));ROW(ТПА[Категория])-1);ROW(ТПА!A1)));"")

=OFFSET(СПИСКИ!$F$2;;;NOW()*0+COUNTIF(СПИСКИ!$F:$F;">""")-1)