Listes dépendantes : un défi

Bonjour !

Dans un onglet "liste ecoles-profs", j'aimerais lister des profs qui donnent leurs cours dans certaines écoles. Pour chaque prof (un par ligne), j'aimerais noter le nom de son école en colonne A, le nom du prof en colonne B, sa discipline en colonne C et quelques infos (téléphone, email etc). Cet onglet servirait de "base de données".

Dans un autre onglet, j'aimerais pouvoir ajouter des lignes dont les seules valeurs possibles dans la colonne A seraient les différentes écoles listées dans la première colonne de l'onglet "liste ecoles-profs". Ensuite, une fois qu'une école a été choisie, on ne pourrait choisir dans la cellule à sa droite QUE les profs qui font partie de l'école sélectionnée... Puis dans les cellules à droite, apparaîtraient automatiquement sa discipline, son téléphone, son email etc...

Je joins un fichier pour vous montrer mieux ce que je souhaiterais faire...

J'ai bien trouvé des choses concernant la validation données, mais rien encore concernant les listes déroulantes dépendantes à ce point là... Est-ce possible ??

Merci pour vos conseils !!

Trucmuche

Bonjour,

Tu n'as que 2 listes de choix, donc seulement le choix2 (prof) dépendant du choix1 (école). Les autres éléments relèvent de la recherche dans une table...

Ta base doit être triée sur le critère école en 1 et le critère prof en 2. C'est actuellement l'inverse dans ton fichier.

A partir de la base, il n'y a donc qu'à extraire une liste de écoles qui alimentera le choix1. Le choix2 sera constitué à partir des 2 premières colonnes de la base au moyen d'une formule adéquate.

Une macro permettra d'opérer les mises à jour lors de changements dans la base (ajouts ou suppression) : tri de la base, extraction de la liste de choix1. Une autre macro est en principe à prévoir pour effacer le choix2 en cas d'effacement du choix1 lors de l'utilisation.

Ensuite, tout dépend des particularités d'utilisation... mais voilà les principes de base de la mise en place de listes-cascade.

Ceci dit, je n'interviendrai pas sur ton fichier dans la mesure où tu utilises des tableaux Excel sur lesquels je n'apprécie pas de travailler..., surtout pour ce genre de projet. Une base de données sans mise en forme particulière (car il ne s'agit pas d'un affichage de présentation, et l'on n'a rien à y faire sauf pour saisir les mises à jour) me paraît toujours préférable.

Cordialement.

@ Trucmuche

Le sujet parait simple, mais il est complexe en réalité :

• d'abord pour la liste déroulante dépendant d'un critère (nom de plage dynamique)
• ensuite la recherche de correspondance sur 2 critères (formule matricielle)

J'ai limité la taille du tableau au juste nécessaire.

A toi de découvrir les astuces et poser des questions éventuelles de compréhension !

Bonjour à vous,

Et merci pour vos interventions, en particulier à Steelson qui a gentiment mis la main à la pâte...

La mise en forme me paraît nécessaire car nous allons travailler sans cesse sur ces tableaux, ajouter des lignes, modifier, trier etc, et donc ce n'est pas qu'une base de données, elle sera aussi utilisée pour l'affichage. Ta réponse, MFerrand, m'est donc relativement inutile car j'ignore comment réaliser les différentes actions dont tu parles conceptuellement, j'essaie d'apprendre et l'exemple m'est donc utile (nécessaire) à ce stade.

J'ai des petites questions... peut-être surtout pour Steelson qui a conçu la chose superbement, il me semble !

1) Pourquoi faut-il appuyer sur la touche TAB pour créer une nouvelle ligne ? Il me semble que si on ajoute (par exemple dans le premier onglet) quelquechose dans la 1e cellule libre de la colonne A, le tableau "listécoles" s'agrandit automatiquement... On risque quelque chose à faire comme cela ou à utiliser la fonction "insérer une ligne" en plein milieu de ce tableau avant d'éventuellement retrier ?

2) je n'ai pas trouvé où "listeprofs" était défini... Et là, je pense que je vais apprendre quelque chose de vraiment utile pour moi...

3) Dans l'onglet attributions, j'ai vu que les cellules contenaient des formules comme : "{=INDEX(ecole_prof[tél];EQUIV([@école]&[@prof];ecole_prof[école]&ecole_prof[prof];0))}". Ca veut dire quoi, l'accolade autour ? Quand j'essaie de recopier cette formule ailleurs, je ne parviens pas à la faire fonctionner et je ne parviens pas à faire accepter à Excel ces accolades...

Merci encore !!

Trucmuche

Bonsoir,

Je me suis contenté de te décrire les fonctionnalités à assurer pour le "produit fini", soit hormis la saisie nécessaire à la mise à jour de la base, tout le reste étant réalisé automatiquement. Tu n'auras par ailleurs qu'à te contenter d'utiliser le système sur ta feuille de travail... Si tu fais état de la nécessité de mise à jour fréquentes, un formulaire de saisie ne serait en outre pas inutile : cela réduit pratiquement à zéro toute intervention manuelle sur la base de données, et offre donc une plus grande sécurisation.

Je ne vais pas me mettre à produire du code dont je n'ai pas besoin, même à titre d'entraînement, alors que tu es en désaccord sur ma proposition au niveau de la conception ! C'est toi qui décide de l'aide que tu veux avoir...

Bonne continuation. Cordialement.

Bonsoir MFerrand,

Je pense simplement que tes compétences dépassent largement les miennes et que je ne pourrais pas apprendre avec ce que tu produirait, même si la solution serait idéale à plein de points de vue (j'en sais rien)... Du coup pour moi, cela ne servira à "rien" car je ne pourrai pas maîtriser les choses suffisamment que pour y apporter des modifications si jamais elles sont nécessaires...

En outre, la solution proposée par Steelson me paraît tout à fait fonctionnelle et suffisante pour ce qu'on a à faire, et beaucoup plus à ma portée pour le moment... je vais déjà apprendre comment cela fonctionne et peut-être qu'un jour, on rediscutera d'autre chose ensemble

Merci en tout cas...

Trucmuche

trucmuche2005 a écrit :

1) Pourquoi faut-il appuyer sur la touche TAB pour créer une nouvelle ligne ? Il me semble que si on ajoute (par exemple dans le premier onglet) quelquechose dans la 1e cellule libre de la colonne A, le tableau "listécoles" s'agrandit automatiquement... On risque quelque chose à faire comme cela ou à utiliser la fonction "insérer une ligne" en plein milieu de ce tableau avant d'éventuellement retrier ?

Si on ne fait pas TAB, la nouvelle ligne n'est pas créée dans le tableau et on ne bénéficie pas du menu déroulant de la première cellule colonne A

Si on ajoute quelquechose dans la 1e cellule libre de la colonne A, je pense que le menu déroulant n'est pas encore activé dans cette case

On peut insérer et supprimer une ligne

trucmuche2005 a écrit :

2) je n'ai pas trouvé où "listeprofs" était défini... Et là, je pense que je vais apprendre quelque chose de vraiment utile pour moi...

Formules > Gestionnaires de noms

=DECALER('liste ecoles-profs'!$A$1;EQUIV(attributions!$A1;ecole_prof[école];0);1;EQUIV(attributions!$A1;ecole_prof[école];1)-EQUIV(attributions!$A1;ecole_prof[école];0)+1)

2 fonctions EQUIV (une avec 1, une avec 0 en dernier paramètre) pour déterminer le sous-ensemble de la colonne A par DECALER

trucmuche2005 a écrit :

3) Dans l'onglet attributions, j'ai vu que les cellules contenaient des formules comme : "{=INDEX(ecole_prof[tél];EQUIV([@école]&[@prof];ecole_prof[école]&ecole_prof[prof];0))}". Ca veut dire quoi, l'accolade autour ? Quand j'essaie de recopier cette formule ailleurs, je ne parviens pas à la faire fonctionner et je ne parviens pas à faire accepter à Excel ces accolades...

accolades = fonction matricielle, validée par Ctrl+Maj+Entrée

ici, excel va créer virtuellement une nouvelle colonne qui concatène école & prof, cette colonne servira à rechercher la ligne (EQUIV) correspondante aux valeurs entrées @école et @prof; on aurait pu passer par la création de cette colonne (moins élégant)

J'espère avoir répondu, n'hésite pas ... j'ai bien dit :

Le sujet parait simple, mais il est complexe en réalité :

• d'abord pour la liste déroulante dépendant d'un critère (nom de plage dynamique)
• ensuite la recherche de correspondance sur 2 critères (formule matricielle)

Bonsoir,

Si tu commences par dévaloriser tes capacités d'assimilation... Nul ne peut rien pour toi en ce domaine !

Tu n'imagines tout de même pas que la proposition de Steelson sera très différente de ce qu'aurait été la mienne, ni qu'elle se révèlera plus simple ! Une liste dépendante reste une liste dépendante, les variantes pour parvenir au même résultat restent relativement mineures.

Sur ce... Bonne continuation.

Bonjour,

Je ne dévalue pas, je suis juste conscient de mon niveau débutant ici et tu me parles de formulaire de saisie et de macros (VB j'imagine) pour l'ajout et la suppression... ce qui me paraissent juste hors de portée pour le moment. J'exprime le fait que je n'ai pas la nécessité d'un formulaire de saisie et qu'il me semble que la proposition de steelson ne fait pas intervenir de macro VB.

J'ai aussi le souhait d'arriver à maîtriser les concepts relativement rapidement. Je n'ai donc pas vraiment le souhait d'investir une semaine de mon temps pour apprendre ce qui me permettrait d'arriver à la solution optimale. Pas maintenant. C'est tout. Je connais bien mes capacités d'assimilation et ce n'est pas cela qui pose problème. Là c'est le temps et le compromis temps vs "utilisabilité concrète" du résultat qui m'intéresse. Je souhaite apprendre juste ce qui me permettrait d'arriver à une solution utilisable, mais pas à la solution optimale que tu sembles rechercher.

Je vais donc approfondir la solution de Steelson qui va bien plus m'aider concrètement que des discours sur les principes & fonctionalités... Je vois l'exemple et je vais approfondir ce qui est utilisé de manière ciblée. Et si j'ai du temps et si le besoin s'en fait sentir, je chercherai un jour à améliorer les choses et on rediscutera peut-être, une fois donc que j'aurai fait une partie du chemin.

bonjour

une contribution (sans tri ;sans tabu ;sans vba ;sans tablo mormoile ..... )

cordialement

comme les rois mages en galilée ..........

tulipe_4 a écrit :

une contribution (sans tri ;sans tabu ;sans vba ;sans tablo mormoile ..... )

Un vrai festival

Bien vu pour l'absence de tri

Les tableaux, oui cela en irrite plus d'un ... je m'y suis mis, il y a quand même de belles fonctionnalités : on dispose de fait de plages dynamiques, et les formules comme les listes de choix se répercutent automatiquement ...

bonjour

ce n'est pas faux; mais je prefere nommer ,car on se retrouve vite fait avec des formule a rallonge ,pas très lisibles ; perturbant pour oeuvrer sur une plage de plusieurs colonnes ,de plus je crois que ça alourdit .......................

cordialement

je prefere nommer ,car on se retrouve vite fait avec des formule a rallonge ,pas très lisibles

Yes ! Et j'ai aussi quelquefois des bizarreries dont il devient difficile de se dépêtrer : transformation auto d'une formule en cours d'écriture avec des noms, lesquels ne correspondent pas aux plages que tu veux définir dans la formule, mais qui reviennent à chaque fois que tu les enlève ; End(xlUp) renvoyant la dernière ligne utilisée d'un tel tableau même si effacée... Pas systématique, mais ce qui ne pose pas moins de problèmes....

Les différentes écoles s'affrontent ... cela enrichit le débat.

Ici le client a demandé expressément un tableau ... et le client est roi !

re

je plaide

"classique mais chic"

donc: ces gadjects ....... a la rigueur pour sommer

cordialement

Ici le client a demandé expressément un tableau ... et le client est roi !

N'ayant pas de patron ni de client qui me paie, c'est moi qui décide de mon travail

Et même si c'était le cas, je garderais une latitude décisionnelle. Je n'ai jamais exécuté d'ordre au seul motif qu'un patron me l'avait ordonné en 40 ans de vie professionnelle...

Par contre, je n'imposerai pas de solution qui ne puisse être maîtrisée par l'utilisateur, et c'est à lui qu'appartiennent les choix fontionnels ou ergonomiques....

Salut à vous,

Je dois vous avouer que je me suis plongé dans la solution proposée par Steelson et que j'essaie d'apprendre... Je la trouve élégante en fait. Mais je ne vous suis pas sur vos discussions, je ne comprends pas trop ce que vous voulez dire

Ayant déjà investi du temps à apprendre les choses utilisées dans la solution de steelson, je souhaiterais pousser cet apprentissage plus loin et donc rester pour le moment avec cette solution là, jusqu'à en trouver des limites...

Je suis en train de me demander comment créer une liste de validation dans l'onglet "attributions" qui, une fois que l'école a été choisie et que la discipline a été choisie, me propose une liste avec uniquement les profs de cette école qui donnent cette matière... Ben je ne suis pas encore arrivé à comprendre comment gérer les deux critères en même temps...

Voici le document actuel. Donc explicitement, on regarde dans l'onglet "attributions", on choisit en E2 une discipline et dans la cellule F2, la liste de choix ne devrait proposer que les profs (du tableau "ecole_prof" dans l'onglet liste ecole-profs) qui travaillent dans l'école en E2 et qui enseignent la discipline E2. (la colonne G serait alors vouée à disparaître)

J'ai essayé quelque chose du genre "=EQUIV([@ECOLE]&[discipline];ecole_prof[école]&ecole_prof[discipline];0)" mais je ne sais pas comment l'intégrer dans un DECALER (qui ne me semble pas la fonction appropriée, ici, puisque les profs à sélectionner ne sont pas forcément contigüs).

Quelqu'un aurait une piste pour m'aider ?

Il faut noter que le problème initial était 2 listes de choix écoles-profs, dont une dépendante. Il est modifié en 3 listes de choix écoles-disciplines-profs, dont 2 dépendantes.

La base n'est dès lors plus dans l'ordre : les colonnes disciplines/profs sont à inverser. Elle est à trier sur les 3 critères (pour avoir des listes ordonnées au bout du compte).

Et il y a lieu d'extraire une table écoles-disciplines pour obtenir la liste de choix disciplines.

Voilà pour le principe. Je n'interfère pas sur le boulot de Steelson.

MFerrand a écrit :

Et il y a lieu d'extraire une table écoles-disciplines pour obtenir la liste de choix disciplines.

Voilà pour le principe. Je n'interfère pas sur le boulot de Steelson.

Que nenni, le compétition, enfin l'émulation continue

trucmuche2005 a écrit :

Ben je ne suis pas encore arrivé à comprendre comment gérer les deux critères en même temps...

Et en effet, comme évoqué par MFerrand, une solution simple d’adaptation consiste à ajouter une colonne "discipline x école" (ou vice versa), mais la solution devient du coup mois élégante

tulipe_4 a écrit :

une contribution (sans tri ;sans tabu ;sans vba ;sans tablo mormoile ..... )

je suis d'ailleurs curieux de découvrir la solution sans tri de Tulipe !

Je trouve in fine qu'avec des tableaux, même si les formules se rellongent, on gagne en lisibilité :

DECALER(ecole_prof[[#En-têtes];[école]];EQUIV(attribution[@[discipline x école]];ecole_prof[discipline x école];0);1;EQUIV(attribution[@[discipline x école]];ecole_prof[discipline x école];1)-EQUIV(attribution[@[discipline x école]];ecole_prof[discipline x école];0)+1)

Comme j'ai vu qu'il y avait déjà une macro, j'en ai profité pour faire le tri quand on sort de "liste ecoles-profs" :

Private Sub Worksheet_Deactivate()
    ActiveWorkbook.Worksheets("liste ecoles-profs").ListObjects("ecole_prof").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("liste ecoles-profs").ListObjects("ecole_prof").Sort. _
        SortFields.Add Key:=Range("ecole_prof[discipline]"), SortOn:=xlSortOnValues _
        , Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("liste ecoles-profs").ListObjects("ecole_prof").Sort. _
        SortFields.Add Key:=Range("ecole_prof[école]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("liste ecoles-profs").ListObjects("ecole_prof").Sort. _
        SortFields.Add Key:=Range("ecole_prof[prof]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("liste ecoles-profs").ListObjects("ecole_prof"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub