Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Not IsEmpty(Cells(Target.Row, 1)) Then
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(Anweisungen!RC[-1],Panels!R5C2:R100C3,2,FALSE))"
Cells(Target.Row, 3).Value = Date
Exit Sub
End If
End If
If Target.Column = 6 Then
If ActiveSheet.Cells(Target.Row, 1).Value <> vbNullString Then
strFind = ActiveSheet.Cells(Target.Row, 1).Value
Set rngFind = ThisWorkbook.Worksheets("Top30").Columns(7).Find(What:=strFind, LookAt:=xlPart)
If Not rngFind Is Nothing Then
ThisWorkbook.Worksheets("Top30").Activate
Top30alleanzeigen
Top30anzeigen
ThisWorkbook.Worksheets("Anweisungen").Activate
Else
With ThisWorkbook.Worksheets("Top30")
q = .Cells(6, 5).CurrentRegion.Rows.Count + 6
.Cells(q, 1).FormulaR1C1 = "=RANK(RC[2],C[2])"
.Cells(q, 2).FormulaR1C1 = "=""" & strFind & " (""&COUNTIFS(Anweisungen!C[-1],""" & strFind & """,Anweisungen!C[3],""ausgezahlt"")& ""x)"""
.Cells(q, 3).FormulaR1C1 = "=SUMIFS(Anweisungen!C[1],Anweisungen!C[-2],""" & strFind & """,Anweisungen!C[2],""ausgezahlt"")"
.Cells(q, 4).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(IFERROR(LEFT(RC[-2],SEARCH("" "",RC[-2],1) -1),RC[-2]),Panels!C[-2],1,0)),""nicht aktiv"",""aktiv"")"
.Cells(q, 6).FormulaR1C1 = "=RANK(RC[2],C[2])"
.Cells(q, 7).FormulaR1C1 = "=""" & strFind & " (€ ""&TEXT(SUMIFS(Anweisungen!C[-3],Anweisungen!C[-6],""" & strFind & """,Anweisungen!C[-2],""ausgezahlt""),""#.##0,00"") & "")"""
.Cells(q, 8).FormulaR1C1 = "=COUNTIFS(Anweisungen!C[-7],""" & strFind & """,Anweisungen!C[-3],""ausgezahlt"")"
.Cells(q, 9).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(IFERROR(LEFT(RC[-2],SEARCH("" "",RC[-2],1) -1),RC[-2]),Panels!C[-7],1,0)),""nicht aktiv"",""aktiv"")"
.Cells(q, 11).FormulaR1C1 = "=RANK(RC[2],C[2])"
.Cells(q, 12).FormulaR1C1 = "=""" & strFind & " (€ ""&TEXT(SUMIFS(Anweisungen!C[-8],Anweisungen!C[-11],""" & strFind & """,Anweisungen!C[-7],""ausgezahlt""),""#.##0,00"") & "")"""
.Cells(q, 13).FormulaR1C1 = "=DATEDIF(VLOOKUP(""" & strFind & """,Panels!R[-107]C[-11]:R[88]C[-4],6,FALSE),TODAY(),""M"")/(COUNTIFS(Anweisungen!C[-12],""" & strFind & """,Anweisungen!C[-8],""ausgezahlt""))"
.Cells(q, 14).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(IFERROR(LEFT(RC[-2],SEARCH("" "",RC[-2],1) -1),RC[-2]),Panels!C[-12],1,0)),""nicht aktiv"",""aktiv"")"
ThisWorkbook.Worksheets("Top30").Activate
Top30alleanzeigen
Top30anzeigen
ThisWorkbook.Worksheets("Anweisungen").Activate
End With
End If
End If
With ThisWorkbook.Worksheets("Anweisungen")
If .Cells(Target.Row, 6) = "j" Then
.Cells(Target.Row, 7) = Date
End If
End With
End If
End Sub