XL 2019 Liste déroulante en fonction de la présence et répartition de poste

[sharkan]

Bonjour,
j'ai besoin de votre aide
je dois répartir des ouviers a des postes en fonction de leur présence.
Je souhaiterai avoir des listes déroulantes en fonction de leur présence ( si 1= présent si 0 = absent).
Ainsi que si un ouvrier est déja mi a un poste qu'il n'apparaisse plus dans la liste déroulante.
Est-ce possible ?
Mes connaissances dans excel sont tres limitées ^^
j'ai mi en piece jointe le fichier que j'ai commencé.
merci

 

[chris]

Bonjour

Les absences doivent faire partie du même tableau que les noms correspondants et les tableaux ne doivent pas contenir de lignes vides

Sauf à trier systématiquement à chaque modification d'une présence et l'utilisation de formules DECALER pour nommer les plages de présents, il faut coder

Une solution combinant PowerQuery pour extraires les présents et quelques lignes de VBA pour actualiser en cas de modification d'une présence

 

[mapomme]

Bonjour @sharkan et bienvenue sur XLD 🙂,
Salut @chris 😉

Une version via VBA.

• Les tableaux structurés ont été remaniés (voir remarque de @chris)
• Des mises en forme conditionnelles (une par colonne B, C et D) colorent la cellule en rouge si un équipier déjà affecté est ensuite rendu absent (pour tenir compte des modifications ultérieures de l'état d'un agent). Voir exemple de l'équipier "personneT-5" en changeant son état de 1 à 0 puis à nouveau à 1.

Le code est dans le module de la feuille "Feuil1". Il est un peu commenté :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ncol_liste&, tList As ListObject, nom, maListe As String, Valeur
   If Intersect(Target, Range("b:d")) Is Nothing Then Exit Sub 'sélection hors colonne B à D, on quitte
   If Target.Count <> 1 Then Exit Sub              'plus d'une cellule sélectionnée, on quitte
   If Target.Row = 1 Then Exit Sub                 'si sélection ent ligne 1 (titres), on quitte
   Valeur = Target                                 'valeur du membre de l'équipe
   Target.Validation.Delete                        'on efface la liste de validation de target
   If Cells(Target.Row, "a") = "" Then Exit Sub    'si en colonne A la valeur est vide, on quitte
   ncol_liste = Range("g1").Column + 3 * (Target.Column - 2)      'n° de la colonne de la liste
   'construction de la liste de validation
   Set tList = Cells(1, ncol_liste).ListObject     ' tableau structuré équipe en fonction de la de la colonne de la cellule sélectionnée
   For Each nom In tList.DataBodyRange.Columns(1).Cells     'pour chaque membre de l'équipe
      If nom <> "" Then    'si le nom du membre n'est pas vide
         If IsError(Application.Match(nom, Columns(Target.Column), 0)) Then   'si le membre n'est pas déjà sue un poste
            If nom.Offset(, 1) = 1 Then maListe = maListe & "," & nom & ","   'on ajoutre le membre à la liste des membres possibles
         End If
      End If
   Next nom
   ' si l'équipier déjà choisi n'est plus parmi les présents, on l'ôte de la liste des possibles
   If Valeur <> "" Then If InStr("," & Valeur & ",", maListe) = 0 Then maListe = Replace(maListe, "," & Valeur & ",", "")
   If maListe <> "" Then      'si la liste des possibles n'est pas nulle
      With Target.Validation
         'on retire la virgule en tête de liste et fin de liste et on remplace les doubles virgules par une simple virgule
         maListe = Replace(Mid(Left(maListe, Len(maListe) - 1), 1), ",,", ",")
         .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=Mid(maListe, 2)  'on définit la liste de validation
      End With
   End If
End Sub

 

[sharkan]

Bonjour @sharkan et bienvenue sur XLD 🙂,
Salut @chris 😉

Une version via VBA.

• Les tableaux structurés ont été remaniés (voir remarque de @chris)
• Des mises en forme conditionnelles (une par colonne B, C et D) colorent la cellule en rouge si un équipier déjà affecté est ensuite rendu absent (pour tenir compte des modifications ultérieures de l'état d'un agent). Voir exemple de l'équipier "personneT-5" en changeant son état de 1 à 0 puis à nouveau à 1.

Le code est dans le module de la feuille "Feuil1". Il est un peu commenté :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ncol_liste&, tList As ListObject, nom, maListe As String, Valeur
   If Intersect(Target, Range("b:d")) Is Nothing Then Exit Sub 'sélection hors colonne B à D, on quitte
   If Target.Count <> 1 Then Exit Sub              'plus d'une cellule sélectionnée, on quitte
   If Target.Row = 1 Then Exit Sub                 'si sélection ent ligne 1 (titres), on quitte
   Valeur = Target                                 'valeur du membre de l'équipe
   Target.Validation.Delete                        'on efface la liste de validation de target
   If Cells(Target.Row, "a") = "" Then Exit Sub    'si en colonne A la valeur est vide, on quitte
   ncol_liste = Range("g1").Column + 3 * (Target.Column - 2)      'n° de la colonne de la liste
   'construction de la liste de validation
   Set tList = Cells(1, ncol_liste).ListObject     ' tableau structuré équipe en fonction de la de la colonne de la cellule sélectionnée
   For Each nom In tList.DataBodyRange.Columns(1).Cells     'pour chaque membre de l'équipe
      If nom <> "" Then    'si le nom du membre n'est pas vide
         If IsError(Application.Match(nom, Columns(Target.Column), 0)) Then   'si le membre n'est pas déjà sue un poste
            If nom.Offset(, 1) = 1 Then maListe = maListe & "," & nom & ","   'on ajoutre le membre à la liste des membres possibles
         End If
      End If
   Next nom
   ' si l'équipier déjà choisi n'est plus parmi les présents, on l'ôte de la liste des possibles
   If Valeur <> "" Then If InStr("," & Valeur & ",", maListe) = 0 Then maListe = Replace(maListe, "," & Valeur & ",", "")
   If maListe <> "" Then      'si la liste des possibles n'est pas nulle
      With Target.Validation
         'on retire la virgule en tête de liste et fin de liste et on remplace les doubles virgules par une simple virgule
         maListe = Replace(Mid(Left(maListe, Len(maListe) - 1), 1), ",,", ",")
         .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=Mid(maListe, 2)  'on définit la liste de validation
      End With
   End If
End Sub

C'est pile poil ce que je voulais je te remercie infiniment !!!

 

[sharkan]

Bonjour

Les absences doivent faire partie du même tableau que les noms correspondants et les tableaux ne doivent pas contenir de lignes vides

Sauf à trier systématiquement à chaque modification d'une présence et l'utilisation de formules DECALER pour nommer les plages de présents, il faut coder

Une solution combinant PowerQuery pour extraires les présents et quelques lignes de VBA pour actualiser en cas de modification d'une présence

merci beaucoup 🙂

 

[sharkan]

mapomme​

Bonjour,
je reviens vers toi cars j'ai un petit soucis avec le fichier excel j'aimerai rajouter deux colonnes à coté de la colonne poste mais lorsque je les rajoutes plus rien ne correspond je dois dire que je ne comprend vraiment pas les codes VBA... 🙁
peux tu m'expliquer comment procéder ?merci