Excel 2016 - liste déroulante sur une cellule d'une autre feuil

Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
- - Dernière réponse : via55
Messages postés
11209
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
17 octobre 2019
- 17 oct. 2019 à 22:43
Bonjour,

je cherche à faire des listes déroulantes sur certaines cellules de la feuil2 en relation avec feuil1
J'ai bien trouvé des tutos et autres exemples mais je n'arrive pas à les appliquer à mon sujet.
ex.
- feuil1 qui est un plan comptable - un compte = 5 chiffres + 1 libellé. les 2 premiers chiffres = le compte principal et les 3 derniers le sous-compte. (2 en A7, A8....A400 et 3 en B7, B8...B400). le libellé en C12,C13...C400)
- feuil2 est une feuille de saisie.
quand je saisis en feuil2 je souhaite voir apparaitre :
- en D12, D13...la liste des comptes principaux ( 2 premiers chiffres) + le libellé des cptes principaux de la feuil1
- en E12, E13....la listes des sous comptes + leur libellé de la feuil1

J'espère avoir été assez clair.

Cordialement

Glaieul
Afficher la suite 

20/31 réponses

Messages postés
24604
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
18 octobre 2019
4836
0
Merci
Bonjour
un modèle anonymisé et avec quelques explications, voire des exemples de ce que vous voulez obtenir, serait le bienvenu
à déposer ici si vous le pouvez:
http://mon-paratge.fr
et revenez coller le lien créé sur le site
à vous lire
crdlmnt
Commenter la réponse de Vaucluse
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
0
Merci
Bonjour vaucluse.
Il n'y aurait pas un problème avec le lien ? ca marque site introuvable.

Cdt
JCB40
Messages postés
1129
Date d'inscription
lundi 2 juillet 2018
Statut
Membre
Dernière intervention
18 octobre 2019
138 -
Bonjour
Effectivement il y a une faute de frappe voir ci-dessous
http://mon-partage.fr
Cordialement
Vaucluse
Messages postés
24604
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
18 octobre 2019
4836 > JCB40
Messages postés
1129
Date d'inscription
lundi 2 juillet 2018
Statut
Membre
Dernière intervention
18 octobre 2019
-
merci JCB, au moins je peux garantir que je suis toujours le roi du clavier!
Commenter la réponse de glaieuls29
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
0
Merci
re bonjour vaucluse.

ci joint le lien pour le fichier test

https://www.cjoint.com/c/IJgjxGnNVPz

cordialement
Commenter la réponse de glaieuls29
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
0
Merci
merci à vous 2. jcb40 à l'oeil et le bon lol

J'espère que mon fichier est lisible

cdt
JCB40
Messages postés
1129
Date d'inscription
lundi 2 juillet 2018
Statut
Membre
Dernière intervention
18 octobre 2019
138 -
Bonjour
Merci à toi
Je laisse Vaucluse s'occuper de ton cas
Bon WE
Vaucluse
Messages postés
24604
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
18 octobre 2019
4836 -
moi je veux bien, mais je renvoie à mon message: "avec quelques explications???"
Nostradamus est mort et là ça fait un peu juste pour trouver où placer les validations et pour savoir sur quelle base on les prends!
Commenter la réponse de glaieuls29
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
0
Merci
re,

merci du temps que tu passes et desole si mes explications ne sont pas tres claires

ex : si sur l'onglet grand journal j'ai saisi 61 en D12 je souhaiterais voir en E13 les sous comptes du compte 61 de l'onglet plan comptable.

est-ce plus clair ?

cdt
Commenter la réponse de glaieuls29
Messages postés
11209
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
17 octobre 2019
1458
0
Merci
Bonjour glaieul

En attendant que Vaucluse (que je salue amicalement au passage) ait fini sa sieste, une proposition
en recréant un tableau des comptes avec 3 plages nommés (la ligne de 2 n°s, la cellule avant la 1ere du tableau, la colonne avant la 1ere du tableau) et des formules avec DECALER dans la validation des cellules avec liste déroulante
https://www.cjoint.com/c/IJgmZDlmgdL

Cdlmnt
Via
glaieuls29
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
-
bonjour Via et merci de te pencher sur mon problème.

je vais essayer de ta façon mais je souhaiterais voir le libellé des comptes et sous-comptes.
De plus le classeur possède une douzaine douzaine d'onglets dont 3 font références à l'onglet plan comptable...je vois essayer de modifier tout ca.

Je n'arrive pas à comprendre pourquoi j'arrive à afficher les 3 chiffres sous comptes et pas les libellés affiliés.

cordialement

Glaieul
Commenter la réponse de via55
Messages postés
11209
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
17 octobre 2019
1458
0
Merci
Re

1 le libellé des comptes ressort bien en colonne G avec ta RECHERCHEV
Par contre je ne sais pas où tu veux aller chercher le libelle comtes analytiques

2 Si d'autres onglets font référence au plan comptable, le même formule de validation dans la cellule de liste déroulante doit donner le même résultat

Cdlmnt
Via
Commenter la réponse de via55
Messages postés
24604
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
18 octobre 2019
4836
0
Merci
Re (et salut Via)
voyez ici, avec les infos et les modifs expliquées en feuil2 car un tableau avec une ligne vide sur deux comme le votre n'est pas la meilleure option pour créer des listes
et par ailleurs les nombres en formats textes sont bien souvent source de soucis dans les recherches
si vous utilisez des formules genres GAUCHE ou construction de texte pour décomposer des N°, multipliez le résultat pas un pour renumériser
ex:
=GAUCHE(A1;2)*1 sinon le résultat est texte
https://mon-partage.fr/f/bhxMS1AN/
crdlmnt
Commenter la réponse de Vaucluse
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
0
Merci
Re bonjour,
Obligations dominicales, je m'étais absentée
avant tout merci à vous de perdre du temps pour moi.

je dois mal exprimer mes souhaits.
Actuellement, à moins de connaitre par coeur le plan comptable, pour saisir une recette ou une dépense je suis obligée de consulter le plan comptable pour choisir l'affectation AVANT la saisie. En effet l'intitulé du compte ne s'affiche pas avec le N° correspondant.
par exemple pour le compte 61 350 (cf :onglet Grand Livre - ligne 16)

en saisissant le 6 ----> souhaits : affichage de tous les comptes qui commencent par 6 avec leurs intitulés.
en saisissant le 1 :------> souhaits : affichage de tous les comptes 61 avec leurs intitulés.
..... et ainsi de suite pour les 3 chiffres suivants.

Cordialement

Glaieul
Commenter la réponse de glaieuls29
Messages postés
24604
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
18 octobre 2019
4836
0
Merci
Alors il faudra passer par VBA car par formule et validation, ça va être trop tordu
Et pour le VBA je vous laisse avec mon ami Via, ce n'est pas ma spécialité
Ceci dit, si vos nombre de codes ne dépassent pas ceux du modèle, est ce vraiment tuile puisqu'il n'apparait dans tous les cas qu'une liste restreinte facilement exploitable?
glaieuls29
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
-
re,
oui je sais bien mais pour le faire évoluer, et surtout pour accroitre mes connaissances car la je peche totalement. je veux comprendre comment on le fait concretement. j'ai beau essayé les tutos et autres je n'y arrivent pas..

merci à toi

glaieul
Vaucluse
Messages postés
24604
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
18 octobre 2019
4836 -
Le mieux pour vous serait alors de créer une base de données et une page où s'afficherait la liste spécifique des comptes ajustée au fur et à mesure de l'entrée de données, et de cliquer au final sur celui qui vous intéresse pour l'intégrer dans la feuille récap
Je confirme que notre ami Via sera meilleurs que moi pour réalisera, je risque de proposer quelque chose de nettement moins performant
Je vous laisse entre ces mains!
glaieuls29
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
-
merci en tout cas pour ton aide.

je fait le "wait and see" pour le moment et au besoin je repenserai tout le pb mais cela me generait tout de meme.

cdt
Commenter la réponse de Vaucluse
Messages postés
11209
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
17 octobre 2019
1458
0
Merci
Pas sur d'avoir tout compris mais voilà un autre essai avec liste déroulante variable selon qu'on entre 1, 2 ... 5 chiffres
https://mon-partage.fr/f/QuFBz3ir/

Cdlmnt
Commenter la réponse de via55
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
0
Merci
Bonjour VIA,

merci pour ces modifications nocturnes. Je ne l'ai pas encore tester en l'intégrant dans mon classeur Excel mais ça m'a l'air super.
La cerise sur le gâteau serait de pouvoir créer ta liste déroulante des intitulés des comptes (colonne F) dans mes colonnes D et E. afin de conserver l'organisation originale de mes colonnes.
En effet dans mon classeur j'ai plusieurs onglets qui font références aux colonnes D et E (résultat, résultat synthétique, bilan, balance....).

Merci encore

Glaieul
Commenter la réponse de glaieuls29
Messages postés
11209
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
17 octobre 2019
1458
0
Merci
Bonjour

Je ne comprends plus trop !
En fait tu voudrais garder les colonnes D, E , F tel quelles ?
En D liste déroulante des 2 premiers chiffres d'un intitulé
En E liste déroulante de 3 derniers chiffres possibles
En F concatenation de D et E
En G affichage du libellé correspondant à ce qui apparait en F

Est cela ou non ?
Commenter la réponse de via55
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
0
Merci
Bonjour VIA.
oui ça ressemblerait a ça. Il y a peut être une colonne redondante ?

j'ai essayé d'adapter tes 2 onglets à mon classeur...(des #ref! qui apparaissaient partout après remplacé mes onglets par les tiens).
j'avais pratiquement tout terminé mais j'ai fait une fausse manip. Dans l'urgence j'ai fait une sauvegarde qui a effacé la précédente.
Maintenant je n'ai plus de libellé en liste déroulante.
(je reprenais ligne par ligne dans tout le classeur. Il doit y avoir une solution plus rapide mais je ne la connais pas).

cdt

glaieul
Commenter la réponse de glaieuls29
Messages postés
11209
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
17 octobre 2019
1458
0
Merci
Je me pencherai ce soir sur une nouvelle version comme indiquée dans mon précédent post
Ce serait bien d'avoir ton fichier original pour mettre en place directement dessus
Si tu peux me le fournir ce serait bien
Par souci de confidentialité tu peux le poster sur mon-partage.fr, copier le lien créé et me fournir ce lien en message privé (clic sur mon pseudo en tête de message pour m'envoyer un mp

Cdlmnt
glaieuls29
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
-
Re bonjour VIA
vraiment merci de ce que tu fais pour moi car je commence à pédaler dans la choucroute si je puis m'exprimer ainsi.

Je dois m'absenter et je te l'envoie au plus vite.

Et vraiment désolée si je n'ai pas été assez claire dans mes explications.

merci

glaieul
Commenter la réponse de via55
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
0
Merci
Bonjour VIA.

A force de ténacité je crois avoir réussi à "adapter" ta version à mon classeur.
(mes connaissances excel sont limitées et du coup pourquoi faire simple quand je peux faire compliqué. Il y a encore quelques semaines j'ignorais toutes ces possibilité de tri)

Par contre ce que je ne comprends pas c'est à quoi fait référence nom "mat" dans gestionnaire des noms. je n'ai que #ref!.
si tu pouvais me donner quelques explications ou à défaut la formule de ce à quoi il fait référence.

Merci à toi de m'avoir aiguiller sur l'organisation du 1er onglet. Je continue et si je cale je n'hésiterais pas à te demander de l'aide avec mon fichier en piece jointe cette fois ci.

Merci d'avance

Glaieul
Commenter la réponse de glaieuls29
Messages postés
11209
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
17 octobre 2019
1458
0
Merci
Bonjour

mat est la plage nommée (voir dans Formules - Gestionnaire de noms) H4:AM4
mais tu a pris les formules du 1er fichier
En voici un autre où la liste deroulante en D s'adapte en fonction du choix en E et les recherchev en G en fonction de la concatenation en F
Plages nommées (cf Gestionnaire de noms) pour la formule de validation de la liste deroulante : depart et intdeb
https://mon-partage.fr/f/xo19ZpU2/

Cdlmnt
Via
Commenter la réponse de via55
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
0
Merci
re,
je progresse mais j'ai encore du boulot :-) !!

je vois très bien le gestionnaire des noms, les validations de données...mais je fais quoi concrètement avec depart et inteb que j'ai bien trouvée dans le gestionnaire de noms.

je patauge dans les formules de validation des données. je bricole je prends ici et la des élément, je bidouille pour voir ce que ça donne...ou pas.

je commence ma formule comment ?

j'imagine pas le temps que tu as du passer sur le fichier et je t'en remercie.

merci encore

Glaieuls
Commenter la réponse de glaieuls29
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
0
Merci
RE,
je n'avais pas compris que tu avais TOUT fait !!!
en testant ton fichier je vois bien que les libellés sont affichés au fur et à mesure.

je m'aperçois que j'utilisais Excel vraiment de façon très basique voire simpliste alors qu'il a des possibilités étonnantes ......à condition de bien maitriser toutes ces formules alambiquées

j'essaie de l'adapter lui aussi pour essayer de comprendre le raisonnement des formules et si je ne réussissais je me permettrai de revenir vers toi.

(juste pour info, peux tu me donner une idée du temps qu'il t'as fallu pour pouvoir faire ça ?)

Merci Beauuuuucoup pour tout ce que tu as fait.

glaieuls
Commenter la réponse de glaieuls29
Messages postés
11209
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
17 octobre 2019
1458
0
Merci
Re,

J'utilise des plages nommées car plus court dans les formules et si à modifier par la suite (plage plus grande par ex) il suffit de modifier une fois dans Gestionnaire de noms au lieu de la faire dans toutes les formules
Formule de validation de données de la plage en colonne E su Grand livre :
=DECALER(depart;EQUIV(D12;intdeb;0);;NB.SI(intdeb;D12))

On DECALE à partir de la plage depart (J6 du Plan comptable) d'un nombre de lignes EQUIValent à la position de la valeur entrée en D12 dans la plage intdeb (I7:I2444 du Plan)puis en hauteur on prend autant de lignes que la valeur en D12 existe dans inteb (comptée avec NB.SI)

Ex : 27 entrée en D12 la formule fait décaler jusqu'à la cellule J21 puisque le 1er 27 trouvé est en I21 et on prend une hauteur de 4 puisqu'il y a 4 fois 27 dans la colonne I, donc la liste déroulante va afficher 000 400 600 680

T’inquiètes pas pour le temps passé, c'est assez rapide quand on maîtrise à peu près ce genre de formules ☺
glaieuls29
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
-
merci VIA55 pour toutes ces explications très intéressantes mais pas très compréhensibles au premier coup d'oeil.

je reviens donc vers toi avec ce petit laïus qui j'espère ne va pas t'effrayer.

je suis toujours au stade d'essayer de comprendre les formules de ton fichier 2 car il me parait plus simple que la 3è version.
Je m'interroge sur les messages d'erreur que je reçois quand je "bidouille"
- 1ER message que je peux avoir : "la liste source doit être délimitée ou faire partie d'une ligne ou d'une colonne"
- 2ème bizarrerie que je peux avoir : affichage de ma formule dans la liste déroulante

Le plus surprenant : en changeant de cellules et en adaptant les formules aux nouvelles cellules des fois ça fonctionne bien. (Je fais des copier/coller des formules que j'ai modifiées dans .Text)

je ne comprends pas ce que je fais de bien ou de pas bien.

AH oui dernier petit truc : comment navigue-t-on facilement dans la formule de validation des données ? avec les flèches c'est la catastrophe ca intercale des N° de cellules.

Pourrais-tu m'éclairer ?

Merci d'avance

Voili voilou -

effet en reprenant ta 2ème version et sur la base de tes formules D12-E12-F12 :
j'ai voulu tester en F2 une liste déroulante avec affichage combiné du numéro+libellé (en adaptant tes formules de D12-E12-F12).

1) j'ai donc créé un nom de la façon suivante
onglet plan comptable -->clic gauche sur l6,m6---> gestionnaire de noms : nouveau --> lib_5 -->grand livre journal--->référence à : inscription automatique (='plan comptable général commenté'!$L$6:$M$6

2) E2 mis la formule (=SIERREUR(INDEX('Plan Comptable Général Commenté'!C:C;EQUIV('Grand Livre Journal'!F2;'Plan Comptable Général Commenté'!D:D;0));"")

3) sélectionné F2--->validation des données : Option--->LISTE--->source ---> =DECALER(lib_5;EQUIV(d2;INDIRECT("m_"&NBCAR(d2));0);;NB.SI(INDIRECT("m_"&NBCAR(d2));d2)).
glaieuls29
Messages postés
66
Date d'inscription
lundi 18 février 2008
Statut
Membre
Dernière intervention
17 octobre 2019
-
re
en me relisant je m'aperçois que j'ai pris quelques libertés dans l'annonce du 1er message d'erreur.

le vrai message d'erreur est le suivant : la liste Source doit être délimitée ou faire référence à une seule ligne ou colonne.
Commenter la réponse de via55