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

Question

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

Vaucluse · Answer

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

glaieuls29 · Answer

Bonjour vaucluse.
Il n'y aurait pas un problème avec le lien ? ca marque site introuvable.

Cdt

glaieuls29 · Answer

re bonjour vaucluse.

ci joint le lien pour le fichier test

 https://www.cjoint.com/c/IJgjxGnNVPz

cordialement

glaieuls29 · Answer

merci à vous 2. jcb40 à l'oeil et le bon lol

J'espère que mon fichier est lisible

cdt

glaieuls29 · Answer

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

via55 · Answer

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

via55 · Answer

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

Vaucluse · Answer

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

glaieuls29 · Answer

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

Vaucluse · Answer

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?

via55 · Answer

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

glaieuls29 · Answer

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

via55 · Answer

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 ?

glaieuls29 · Answer

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

via55 · Answer

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 · Answer

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

via55 · Answer

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

glaieuls29 · Answer

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

glaieuls29 · Answer

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

via55 · Answer

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 ☺

via55 · Answer

Bonjour glaieul

Je rentre du boulot ☺

1) Une liste déroulante ne peut comme le message d'erreur te le rappelle ne faire référence qu'à une colonne ou une ligne
Si par ex tu as des noms en col A et des prénoms en col B tu ne peux pas créer une liste déroulante basée sur A:B affichant Nom + prénom
Pour cela il faut passer par une 3eme colonne C dans laquelle tu concatènes pour chaque ligne Nom & Prénom et tu bases ta liste déroulante sur cette colonne

2) Affichage de la formule dans la cellule
Pour afficher la formule au lieu de la valeur  Ruban Formules > Afficher les formules. Pour faire l'inverse : afficher les valeurs à la place des formules, il suffit de cliquer à nouveau sur le bouton. mais ça doit le faire pour toutes les formules
Il se peut aussi que ta cellule soit en Format Texte, à vérifier
Il peut s'agir enfin d'un bug, cela m'est arrivé une fois et je n'ai pu m'en sortir qu'en recréant une feuille

3) Pour te déplacer dans la formule tu surlignes les texte à la souris et tu déplaces la souris vers la droite (ou vers la gauche)

Le fichier qui est le plus abouti et qui évite de passer par plusieurs colonnes intermédiaires est néanmoins le 3

Cdlmnt
Via

via55 · Answer

Bonjour Glaieul

1) ='Plan Comptable Général Commenté'!$L4:$M4 n’est pas valable car fait référence à 2 colonnes au lieu d'une seule

2) Les références doivent bouger car par ex lib-4 a comme référence ='Plan Comptable Général Commenté'!$M4, la colonne est bloquée par le $ mais pas la ligne ce devrait être ='Plan Comptable Général Commenté'!$M$4

Cdlmnt
Via

via55 · Answer

Re

Re, 

je n'ai pas plus d'explications que toi pourquoi cela fonctionne une fois!
Cela ne devrait même pas !

Si tu tiens à faire afficher dans la liste déroulante le code et le libellé il faut passer par une colonne de concaténation pour la liste déroulante puis par une macro pour ne récupérer que le libellé, voir exemple ici  Liste avec 2 colonnes :
http://boisgontierjacques.free.fr/pages_site/donneesvalidation.htm#Liste2Colonnes

Dans ton cas la macro serait
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect([G12:G999], Target) Is Nothing And Target.Count = 1 Then
    Application.EnableEvents = False
    Target = Right(Target, Len(Target) - 6)
    Application.EnableEvents = True
  End If
End Sub
Cdlmnt
Via

via55 · Answer

Voilà ce que donne la méthode de Boisgontier appliquée à ton fichier en créant donc une nouvelle colonne N au Plan comptable sur laquelle baser la liste déroulante et en appliquant une macro à la feuille Grand livre, ce n'est pas trop compliqué  :
https://mon-partage.fr/f/N7TVgp1C/

Cdlmnt
Via

via55 · Answer

re

1) il faut modifier la formule en colonne N du Plan pour inverser
=L7&" "&M7 devenant  =M7&" "&L7 

2) dans la macro associée à la feuille Grand livre modifier la ligne
Target = Right(Target, Len(Target) - 6)
en
Target = Left(Target, Len(Target) - 6)
puisque désormais il faut prendre à gauche et non plus à droite de la chaine de caractères  un nombre de caractère égal à la longueur (Len) de la chaîne -6 puisqu'il faut exclure l'espace et les 5 chiffres

Cdlmnt
Via

via55 · Answer

Comme cela alors ?
https://mon-partage.fr/f/0mRqoOte/

Mais quid de la colonne E qui est vide ?

Cdlmnt
Via

via55 · Answer

Bonjour

la colonne G comporte une formule INDEX qui renvoie bien le bon n°
la liste déroulante en col G devient inutile il faut la supprimer
on pourrait aussi dans la macro qui renvoie le libellé renvoyer le code complet en G et plus de formules en col G

Quant à l'onglet Recap chez moi les formules ont bien l'air de s'être mises à jour et j'ai les même chiffres en retour que dans les fichiers précédents apparemment

via55 · Answer

oui puisque la macro existe déjà pour extraire le libellé on peut dans la même macro extraire le n° et le faire inscrire en col G ou ailleurs pour avoir le n° complet qui n'existe pas ailleurs
mais si tu as supprimé la col G ?!

Un bouton d'impression peut être utile c sur

via55 · Answer

Bonjour

On va pouvoir régler les deux en une seule fois avec la protection de la feuille

1) Par défaut en principe toutes les cellules sont verrouillées donc sélectionne les colonnes A à G puis clic droit Format de cellules -onglet Protection décocher Verrouillé
puis selectionne I à L et même procédure pour déverrouiller ces colonnes
La colonne H et les colonnes M et suivantes avec des formules restent verrouillées
2) Protéger la feuille : dans le ruban  Revison - Proteger la feuille - décoche  Selectionner les cellules verrouillées, ne laisser coché que Selectionner cellules déverrouillées
Valider (tu peux aussi mettre un mot de passe mais attention à ne pas l'oublier !)
Les formules sont maintenant protégées
3) Pour retourner en début de tableau aller dans Fichier - Options (tout en bas) -- Options avancées -
Deplacer la sélection après validation - Sens : choisir Droite

Cdlmnt
Via

via55 · Answer

Bonjour

Exact, comme la macro scinde la valeur entrée pour ne retenir que le libellé elle bug si la cellule est vide
Pour pallier cela complète la 1ere ligne de la macro par la partie en gras
  If Not Intersect([F12:F999], Target) Is Nothing And Target.Count = 1 And Target.Value <> "" Then

Cdlmnt
Via

via55 · Answer

De rien ☺

J'avais compris kenavo

Bonne suite et bon vent ☺
Et n'hésites pas à me resolliciter si nécessaire

Cdlmnt
Via

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

31 réponses

Discussions similaires