=SI(LIGNES(A$11:A11)=1;0;A11+1)
=SOMMEPROD(ESTNUM(C$9:S$9)*ESTNUM(Tableau1[@[Colonne3]:[Colonne19]]);C$9:S$9;Tableau1[@[Colonne3]:[Colonne19]])
=SIERREUR(T12/SOMMEPROD(ESTNUM(C$9:S$9)*ESTNUM(Tableau1[@[Colonne3]:[Colonne19]]);C$9:S$9);"")
=SIERREUR(RANG(U12;[Colonne21]);"")
Private Sub Worksheet_Change(ByVal Target As Range)
If ListObjects.Count = 0 Then Exit Sub
Dim r As Range
Set r = ListObjects(1).Range
For Each r In r.Rows(r.Rows.Count).Cells
If r(0).HasFormula And r.FormulaR1C1 <> r(0).FormulaR1C1 _
Then r.EntireRow.Delete: Exit Sub
Next
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If ListObjects.Count = 0 Then Exit Sub
Dim r As Range, c As Range
Set r = ListObjects(1).Range
For Each c In r.Rows(r.Rows.Count).Cells
If c(0).HasFormula And c.FormulaR1C1 <> c(0).FormulaR1C1 _
Then c.EntireRow.Delete: Exit For
Next
If r.Rows(r.Rows.Count).Row + 1 = [MOYENNES].Row Then _
[MOYENNES].Cut [MOYENNES].Rows(2) 'les moyennes se décalent
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If ListObjects.Count = 0 Then Exit Sub
Dim br As Range, r As Range, w As Worksheet
Set br = ListObjects(1).DataBodyRange
'---empêche la modification des formules du tableau---
Set r = Intersect(Target, br)
If Not r Is Nothing Then
For Each r In r
With Intersect(br.Rows(1), r.EntireColumn)
If .HasFormula And r.FormulaR1C1 <> .FormulaR1C1 _
Then r.FormulaR1C1 = .FormulaR1C1
End With
Next
End If
'---décalage des moyennes---
If br.Rows(br.Rows.Count).Row + 1 = [MOYENNES1].Row Then _
[MOYENNES1].Cut [MOYENNES1].Rows(2)
'---recopie le nom de l'élève dans la feuille du 2ème trimestre---
Set r = Intersect(Target, br.Columns(2))
If Not r Is Nothing Then
For Each w In Worksheets
If LCase(w.Name) Like "2*trim*" Then Exit For
Next
With w.ListObjects(1).DataBodyRange
For Each r In r
If r <> "" And IsError(Application.Match(r, .Columns(2), 0)) _
Then .Cells(.Rows.Count + 1, 2) = r
Next
End With
End If
End Sub
Lorsque je rempli la 3ème ligne de la feuille trim1, les autres feuille reste avec le nom de la 3ème ligne.
'---décale les moyennes---
If br.Rows(br.Rows.Count).Row + 1 = [MOYENNES1].Row Then
[MOYENNES1].EntireRow.Insert
[MOYENNES1].EntireRow(0).Borders.LineStyle = xlNone
End If