XL 2013 Afficher une liste de données selon la sélection d'une liste déroulante

[Comfortably_Numb]

Bonjour à tous,

Je souhaite savoir si il est possible d'afficher la liste de mes sites (=UE) selon la direction régionale (=DR) sélectionnée en liste déroulante ?

L'idée serait de faire apparaître dans l'onglet "LISTE" la liste des UE correspondant à la DR sélectionnée dans l'onglet "Rapport".

Cela est-il possible sans macro ? J'ai cru comprendre qu'il fallait utiliser le gestionnaire de nom, qui peut m'expliquer ?

Merci à tous pour votre aide

 

[Iznogood1]

Bonjour,

C'est pas forcément simple.
Dans le fichier ci-joint, regarde l'onglet liste colonne M.

Les autres colonnes expliquent la démarche. Elles peuvent être effacées.

Le gestionnaire de nom est utilisé pour construire des listes dynamiques et simplifier la présentation des formules.
On pourrait s'en passer en saisissant l’adresse des plages (si le contenu ne varie pas), ou en utilisant la fonction "Tableau".


Le principe:
On construit une matrice contenant les lignes de la table source correspondant au critère, ou la un "grand nombre" dans le cas contraire (j'ai choisi 10000 dans mon exemple).
Ensuite on classe cette matrice par ordre croissant.
Puis on extrait les données de la source basées sur ces numéros de ligne.


Ce qui donne :
{=SIERREUR(INDEX(UEList;PETITE.VALEUR(SI(DRList=Choix;LIGNE(DRList);100000);LIGNE(DRList)-1);1);"")}
Attention, formule matricielle à valider par CRTL + Shift + Entrée

Avec
Choix = Rapport!$G$18 (la DR séléctionnées en page d'accueil)
UEList = DECALER(DATA_UE!$C$1;1;0;NBVAL(DATA_UE!$C:$C)-1;1) (la liste des UE)
DRList = DECALER(DATA_UE!$C$1;1;1;NBVAL(DATA_UE!$C:$C)-1;1) (la liste des DR)


Décomposons :
SI(DRList=Choix;LIGNE(DRList);100000);LIGNE(DRList)-1)
Si la DR correspond à la sélection, on trouve son numéro de ligne (le -1 est ici car la liste commence à la ligne 2 dans l'onglet DATA_UE, la ligne 1 correspondant aux titres)
Sinon, on retourne un gros nombre (10000 arbitrairement)

PETITE.VALEUR(matrice, LIGNE(DRList))
Comme LIGNE(DRList) va retourner 1 / 2 / 3 ...
PETITE.VALEUR va retourner les lignes dans l'ordre croissant.
Celles correspondant à la DR choisie sont forcément en tête de liste (les autres ont 10000 comme numéro de ligne)

INDEX(UEList ; ligne; colonne)
On trouve les données de la plage UEList
ligne = extraction précédente
colonne 1 (UEList ne comporte qu'une seule colonne).

SIERREUR(extration; "")
Comme on va tenter d'extraire la ligne 10000 qui n'existe pas, on masque simplement le message d'erreur.


Avantage:
Pas de macros

Inconvénient:
La plage d'extraction est fixe et correspond à la plage dans laquelle tu as saisi la formule matricielle.
Donc si tu réserves 10 lignes, et qu'il y a 12 valeurs à extraire, 2 valeurs seront ignorées.
Inversement, si seules 3 valeurs sont extraites, tu auras forcément 7 lignes vides.

 

[sylvanu]

Bonjour Comfortably_Numb, Iznogood

en PJ une version avec construction liste par VBA en automatique.

Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [DR]) Is Nothing Then
        NbLig = Application.CountIf(Sheets("DATA_UE").Range("D:D"), "*")
        NomDR = [DR]
        tablo = Sheets("DATA_UE").Range("D2:D" & NbLig)
        Debut = 0: Fin = 0
        ' Debut liste
        For i = 1 To UBound(tablo)
            If tablo(i, 1) = NomDR Then
                Debut = i
                Exit For
            End If
        Next i
        ' Fin liste
        For i = UBound(tablo) To 1 Step -1
            If tablo(i, 1) = NomDR Then
                Fin = i
                Exit For
            End If
        Next i
        ' Tranfert dans Nom=Liste
        ActiveWorkbook.Names.Add Name:="Liste", _
        RefersTo:="=DATA_UE!$C$" & Debut + 1 & ":$C$" & Fin + 1
    End If
End Sub

 

[Comfortably_Numb]

Bonjour,

C'est pas forcément simple.
Dans le fichier ci-joint, regarde l'onglet liste colonne M.

Les autres colonnes expliquent la démarche. Elles peuvent être effacées.

Le gestionnaire de nom est utilisé pour construire des listes dynamiques et simplifier la présentation des formules.
On pourrait s'en passer en saisissant l’adresse des plages (si le contenu ne varie pas), ou en utilisant la fonction "Tableau".


Le principe:
On construit une matrice contenant les lignes de la table source correspondant au critère, ou la un "grand nombre" dans le cas contraire (j'ai choisi 10000 dans mon exemple).
Ensuite on classe cette matrice par ordre croissant.
Puis on extrait les données de la source basées sur ces numéros de ligne.


Ce qui donne :
{=SIERREUR(INDEX(UEList;PETITE.VALEUR(SI(DRList=Choix;LIGNE(DRList);100000);LIGNE(DRList)-1);1);"")}
Attention, formule matricielle à valider par CRTL + Shift + Entrée

Avec
Choix = Rapport!$G$18 (la DR séléctionnées en page d'accueil)
UEList = DECALER(DATA_UE!$C$1;1;0;NBVAL(DATA_UE!$C:$C)-1;1) (la liste des UE)
DRList = DECALER(DATA_UE!$C$1;1;1;NBVAL(DATA_UE!$C:$C)-1;1) (la liste des DR)


Décomposons :
SI(DRList=Choix;LIGNE(DRList);100000);LIGNE(DRList)-1)
Si la DR correspond à la sélection, on trouve son numéro de ligne (le -1 est ici car la liste commence à la ligne 2 dans l'onglet DATA_UE, la ligne 1 correspondant aux titres)
Sinon, on retourne un gros nombre (10000 arbitrairement)

PETITE.VALEUR(matrice, LIGNE(DRList))
Comme LIGNE(DRList) va retourner 1 / 2 / 3 ...
PETITE.VALEUR va retourner les lignes dans l'ordre croissant.
Celles correspondant à la DR choisie sont forcément en tête de liste (les autres ont 10000 comme numéro de ligne)

INDEX(UEList ; ligne; colonne)
On trouve les données de la plage UEList
ligne = extraction précédente
colonne 1 (UEList ne comporte qu'une seule colonne).

SIERREUR(extration; "")
Comme on va tenter d'extraire la ligne 10000 qui n'existe pas, on masque simplement le message d'erreur.


Avantage:
Pas de macros

Inconvénient:
La plage d'extraction est fixe et correspond à la plage dans laquelle tu as saisi la formule matricielle.
Donc si tu réserves 10 lignes, et qu'il y a 12 valeurs à extraire, 2 valeurs seront ignorées.
Inversement, si seules 3 valeurs sont extraites, tu auras forcément 7 lignes vides.

Bonjour @Iznogood1, tout d'abord merci pour ce travail et surtout les explications ! Je suis bluffé !

J'ai adapté ta formule à mon fichier source qui était épuré au max (infos personnelles etc), tout fonctionne à merveille !

Un grand merci pour ton aide et la solution apportée !

 

[Iznogood1]