XL 2019 Fonction DECALER , avec une liste de valeurs

[Tieum78]

Bonjour ,
besoin d'un petit coup de pouce avec la fonction DECALER

Je joins mon fichier exemple.

J'ai sur la ligne 6 , une liste de participants , dont le nombre est variable , on peut en ajouter , en supprimer.
La particularité c'est qu'on utilise sur cette ligne 6 des cellules fusionnées : chaque participant est donc saisi dans un groupe de 3 cellules fusionnées. Le participant 1 entre la cellule B6 et D6 , le participant 2 entre E6 et G6 , etc...

Je voudrais maintenant, avoir en cellule B10 par exemple , une liste déroulante avec l'ensemble des Participants.
J'utilise une liste définie via la fonction DECALER.
=DECALER(Feuil1!$B$6;0;0;;NBVAL(Feuil1!$6:$6))
Seulement , je ne recupère pas tous mes participants , et il y a systématique deux blancs entre chaque participant

=> Remarque , je cherche donc comment modifier la fonction DECALER pour qu'elle me renvoie uniquement la liste des Participants (sans utiliser de zone de stockage)

Merci par avance

 

[JHA]

Bonjour à tous,

A essayer

JHA

 

[Tieum78]

Bonjour à tous,

A essayer

JHA

Bonjour JHA,

merci pour ta proposition
le résultat obtenu est ok, mais les conditions ne sont pas respectées car tu as utilisé un nouvel onglet "LISTE"
J'aimerais , si possible , obtenir le même résultat mais en modifiant seulement la fonction DECALER

 

[Hasco]

Bonjour,

Il me semble que excel 2019 connaît la fonction FILTRE.
En B5 la liste masquée par une couleur écriture comme le fond et établie par la fonction filtre
Le nom 'ListParticipant' =Feuil1!$B$5# (Dièse compris et obligatoire)

sinon formule matricielle liste des participants sans la fonction filtre :

=INDEX(6:6;AGREGAT(15;6;COLONNE(6:6)/(6:6<>"");LIGNE(INDIRECT("1:"&NBVAL(6:6)))))

Pour en avoir une version horizontale :

=TRANSPOSE(INDEX(6:6;AGREGAT(15;6;COLONNE(6:6)/(6:6<>"");LIGNE(INDIRECT("1:"&NBVAL(6:6))))))

Cordialement

 

[Jacky67]

Bonjour ,
besoin d'un petit coup de pouce avec la fonction DECALER

=> Remarque , je cherche donc comment modifier la fonction DECALER pour qu'elle me renvoie uniquement la liste des Participants (sans utiliser de zone de stockage)

Bonjour à tous,
Ceci est (sauf erreur) impossible sans liste intermédiaire ou vba
Une solution par vba (toute version Xl)

 

[job75]

Bonjour Tieum78, JHA, Hasco, Jacky67,

Je suis sur Excel 2019 et je n'ai pas la fonction FILTRE.

Autre solution VBA avec cette macro dans le code de la feuille :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range, liste$
Set r = Intersect(Rows(6), UsedRange)
With [B10]
    .Validation.Delete 'RAZ
    If ActiveCell.Address <> .Address Or r Is Nothing Then Exit Sub
    For Each r In r
        If r <> "" Then liste = liste & "," & r
    Next
    If liste <> "" Then .Validation.Add xlValidateList, Formula1:=Mid(liste, 2)
End With
End Sub

A+

 

[Tieum78]

Merci à tous pour vos propositions , rapides et de qualité.
Vous gérez.