Liste déroulante dans le désordre

**tsgri** · 04/08/2009, 16h35

Bonjour,

J'ai un onglet avec ma table de données "BDD" avec 2 colonnes A = nom des societes B= noms des contacts et j'ai "défini des noms" pour la colonne nom : "bd_nom", et un onglet pour la consultation.

Sur "consultation" ou j'ai inséré un contrôle de formulaire (sans macro) une liste déroulante ou apparaissent donc la liste des noms (le format de contrôle = plage de données: "bd_nom" liée à ma cellule "A1" )

Le problème c'est que dès que dans ma table "BDD" lje fais un tri alphabétique sur ma colonne société, alors dans la liste déroulante de mon onglet "consultation" les noms apparaissent dans le désordre.

Comment faire pour que, quelque soit l'ordre de tri fait dans ma table "BDD" , les noms apparaissent dans la liste deroulante toujours en ordre alphabétique.

Merci de votre aide.

**ullan** · 04/08/2009, 20h22

Bonjour,

Créer une colonne supplémentaire qui trie les noms par ordre alphabétique et qui sert de source à la liste de validation.
Sans cette colonne supplémentaire je n'ai pas de solution par formules à te proposer.

Si tu ne t'en sors pas, mets un extrait de ton fichier en pièce jointe, reprenant les données et le résultat que tu souhaites obtenir.

**tsgri** · 05/08/2009, 11h44

Ullan,

Le fait d'ajouter une colonne dans ma BDD n'est pas possible car cette table est aussi exploiter pour consulter tous les contacts , faire des filtres etc.....(ma base réelle contient 30 colonnes et 600 lignes) donc a priori je ne vous pas comment gérer ta proposition.

je te joins le fichier pour voir mieux ma problèmatique sur les 3 points (liste en désordre et quelles fonctions ? ) d'ailleurs.

merci

**ullan** · 05/08/2009, 16h09

Bonjour,

Sans colonnes supplémentaires je ne vois pas comment faire.
Les colonnes BDD!F:G peuvent être masquées ou placées plus à droite sur la feuille, ou même sur une autre feuille.

Vois si la proposition en pièce jointe peut te convenir.

**tsgri** · 05/08/2009, 17h32

Ullan,

Pour les problèmes 2 et 3 merci beaucoup , je n'arrivais pas a cumuler les fonctions SI et RECHERCHE donc merci mais je vais juste revoir si je peux cumuler avec DECALER car jorsque que j'insère une nouvelle ligne dans ma base toutes tes cellules passe en #NA.

Par contre pour le problème 1, sur le tri j'ai testé et ca le fait pas vraiment cela me sort de #N/A ce qui ne s'arrange pas lorsque j'unsère de nouvelles lignes, je vais regarder en essayant de cumuler avec DECALER mais à mon avis je pense qu'il doit y avoir une autre solution directement à partir du contrôle de formulaire ou de la cellule "A1";

merci

**ullan** · 05/08/2009, 17h41

Re,

Mais pourquoi insérer des lignes, alors qu'il te suffit de saisir les nouvelles données au bas du tableau existant, puis de le trier.

Quant à : "je pense qu'il doit y avoir une autre solution" , j'espère que tu vois juste, car moi aussi j'aimerais savoir comment faire.
Vas voir sur le site de Jacques Boisgontier, peut-être y trouveras-tu réponse à ta question.

Invité · 05/08/2009, 20h40

Bonsoir,

Combobox dans le tableur:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Private Sub ComboBox1_DropButtonClick()
  Dim temp()
  temp = Range("listeComboTrié")
  Call Tri(temp, 1, UBound(temp, 1))
  Me.ComboBox1.List = temp
End Sub
 
Sub Tri(a(), gauc, droi) ' Quick sort
  ref = a((gauc + droi) \ 2, 1)
  g = gauc: d = droi
  Do
    Do While a(g, 1) < ref: g = g + 1: Loop
    Do While ref < a(d, 1): d = d - 1: Loop
      If g <= d Then
         temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
         g = g + 1: d = d - 1
      End If
   Loop While g <= d
   If g < droi Then Call Tri(a, g, droi)
   If gauc < d Then Call Tri(a, gauc, d)
End Sub

ListBox dans un userform:

Liste est le nom de champ contenant les noms à trier.

Liste =DECALER($B$2;;;NBVAL($B:$B)-1)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Private Sub UserForm_Initialize()
  Dim temp()
  temp = Range("liste")   ' liste tableau temp (1 To n,1 To 1) ou temp = Range([B2], [B2].End(xlDown)) 
  Call tri(temp, 1, UBound(temp, 1))
  Me.ListBox1.List = temp
End Sub
 
Sub tri(a(), gauc, droi)          ' Quick sort
 ref = a((gauc + droi) \ 2, 1)
 g = gauc: d = droi
 Do
     Do While a(g, 1) < ref: g = g + 1: Loop
     Do While ref < a(d, 1): d = d - 1: Loop
     If g <= d Then
       temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
       g = g + 1: d = d - 1
     End If
 Loop While g <= d
 If g < droi Then Call tri(a, g, droi)
 If gauc < d Then Call tri(a, gauc, d)
End Sub

JB

**tsgri** · 05/08/2009, 21h59

Bonsoir,

Il semble donc que malgré les innombrables FONCTIONS d'excel, les macros restent innévitables sur certains objectifs.

Ullan tu as certainement raison et ta remarque me fais découvrir que j'ai des mauvaise habitudes de saisies pour entrer les nouveaux contacts dans ma base de données.....oh damned ! j'y remedie a partir de ce jour. Merci !

Merci à JBG pour son intervention inopinément surprenante .....?!? , n'ayant pas le fichier sous la main je travaille dessus demain.

Merci

**tsgri** · 12/08/2009, 12h54

Bonjour,

Avec la macro je n'y arrive pas ca colle pas.
Alors si quelqu'un a une autre solution plus évidente pour faire un tri dans ma liste déroulante de format de contrôle, je suis toujours preneuse car j'essaie mais je ne trouva pas de solution. et c'est très bloquant car ma table de données je ne peux pas la figer on s'en sert pour des filtres, pour faire des tris régulièrement pas colonne et il y en a une vingtaine...

Merci.

**tsgri** · 12/08/2009, 14h22

Envoyé par ullan

Bonjour,

Sans colonnes supplémentaires je ne vois pas comment faire.
Les colonnes BDD!F:G peuvent être masquées ou placées plus à droite sur la feuille, ou même sur une autre feuille.

Vois si la proposition en pièce jointe peut te convenir.

Bonjour Ullan,

Super de m'avoir montrer le chemin j'ai adpaté a ma base réelle et j'ai compris certaines choses merci. cependant j'ai encore des questions :

Sur le point 2 avec =SI(SOMMEPROD((BDD!A$2:A$11=B$7)*(BDD!B$2:B$11=A11));INDIRECT("BDD!C"&SOMMEPROD((BDD!A$2:A$11=B$7)*(BDD!B$2:B$11=A11)*LIGNE(B$2:B$11)));"")

1) Quelle fonction dois-je utiliser si j'ai plusieurs contact du même service (ex 2 DG), car du coup j'ai le résultat #N/A qui apparait. En fait dans ces cas il faut donc soit qu'il ajoute les données en lignes ou en colonnes. Finalement étant donné que je souhaite avoir tous les collaborateurs qui sont sur le même site que le contact avec des infos sur eux en colonne, il faut peut être que j'utilise une fonction plus globale qui sana être liée aux cellules societé et service, affichera une liste qu'elle soit de 5, 8 10 lignes ? dans ce cas là dois je utiliser les filtres personnalisés ?

2) quand la table est trié par nom, nickel les cellules affichent bien les collaborateurs par contre si je fais dans ma table un tri par société et bien les cellules de resultats affichent toutes #N/A.

3) si FAUX comment faire apparaitre "pas de contact" j'ai essayé en mettant le texte entre les 2 derniers "" mais ca ne marche pas.

Merci

**ullan** · 12/08/2009, 14h37

Bonjour,

Question 2 :
Cela dépend de la façon dont les données sont structurées sous BDD!
Un petit exemple en pièce jointe permettrait peut-être de te proposer une solution.

Question 3 :
Je ne parviens à pas à reproduire le problème.
Ici aussi un exemple serait bien utile, reprenant le tableau avant et après tri, ainsi que les résultats obtenus.

**tsgri** · 13/08/2009, 13h08

Bonjour,

Voici un fichier en exemple, mais du coup je n'arrive même plus a avoir un résultat avec les cellules pleines.

Je tourne vraiment en rond sur ces points là.

Merci de votre aide;

**ullan** · 13/08/2009, 16h13

Bonjour,

Vois les formules corrigées en pièce jointe.
Sous des formules matricielles (SOMMEPROD en est une), les plages auxquelles il est fait référence doivent être de taille identique, ne peuvent faire référence à des colonnes entières (de type A:A) et ne peuvent renvoyer que des valeurs numériques.
C'est pourquoi la formule renvoie le numéro de la ligne et la fonction INDIRECT récupère la valeur alphabétique dans la colonne précisée sous la formule pour la ligne renvoyée par la formule.

Pour les autres questions les explications sont reprises sous la pièce jointe.

**tsgri** · 13/08/2009, 16h32

Bonjour Ullan,

Sur la codification du service, je ne peux absolument pas la changer non seulement car la base sert aussi pour des TCD déjà formaté, mais en plus car il y 600 contacts.
Donc pour les sociétés qui ont plusieurs contacts, par exemple en "DRH" par exemple"), je vais continuer à chercher la fonction adequate.

Merci pour ton aide.

**tsgri** · 13/08/2009, 16h37

Ullan,

Une précision encore s'il te plaît : la fonction LIGNE dans la formule, peux tu me l'expliquer, je vois à quelle plage elle corrrespond, mais je n'arrive pas trop à la comprendre.
merci

**ullan** · 13/08/2009, 17h04

Re,

Comme expliqué sous la dernière pièce jointe, la fonction SOMMEPROD ne peut renvoyer que des valeurs numériques.
Dans le cas qui nous préoccupe il faut renvoyer une valeur texte.
Mettre comme argument LIGNE($1:$20) fait que la fonction renvoie le numéro de la ligne sur laquelle les autres arguments de la formule sont trouvés.
Connaissant la colonne dans laquelle il faut chercher et le numéro de la ligne, le fonction INDIRECT permet de renvoyer la valeur texte.

J'espère avoir été clair cette fois-ci.

Pour l'autre question, il y aurait moyen de récupérer les différents noms par la fonction DECALER (une petite à gaz !), pour autant que les noms cherchés (par exemple Direction) soient les uns sous les autres.
Si ce n'est pas le cas, il y a aussi moyen de le faire par l'intermédiaire d'une colonne supplémentaire, à condition qu'il n'y ait pas deux recherches simultanées.

**tsgri** · 14/08/2009, 15h24

Bonjour,

Comme tu l'as vu sur l'autre discussion auquelle j'avais répondu à ta remarque.... après tes bons conseils qui m'ont fait comprendre la fonction SOMMEPROD et qui est une solution pour travailler ma base sans macro, je suis cependant en de mettre en application la suggestion de coupler un TCD avec macro qui malgré le fait qu'ilai macro, me semble la plus adaptée.

Par contre je ne rentre pas dans lesdétails, il suffit d'aller voir ou tu sais ;
mes discussions vont fusionnées mais je ne sais pas dans quelle sens ?!.

**tsgri** · 19/08/2009, 16h33

Envoyé par Boisgontier

Bonsoir,

ListBox dans un userform:

Liste est le nom de champ contenant les noms à trier.

Liste =DECALER($B$2;;;NBVAL($B:$B)-1)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Private Sub UserForm_Initialize()
  Dim temp()
  temp = Range("liste")   ' liste tableau temp (1 To n,1 To 1) ou temp = Range([B2], [B2].End(xlDown)) 
  Call tri(temp, 1, UBound(temp, 1))
  Me.ListBox1.List = temp
End Sub
 
Sub tri(a(), gauc, droi)          ' Quick sort
 ref = a((gauc + droi) \ 2, 1)
 g = gauc: d = droi
 Do
     Do While a(g, 1) < ref: g = g + 1: Loop
     Do While ref < a(d, 1): d = d - 1: Loop
     If g <= d Then
       temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
       g = g + 1: d = d - 1
     End If
 Loop While g <= d
 If g < droi Then Call tri(a, g, droi)
 If gauc < d Then Call tri(a, gauc, d)
End Sub

JB

Bonjour,

J'ai donc adapté ce 2ème code comme comme ci-dessous mais cela ne marche pas. Dès que je tri mon tableau par société, alors cela a une incidence sur maliste déroulante ou les noms apapraissent selon l'ordre des sociétés. (Je suis également allé sur le site de Bois-Gontier mais pas plus réussie).
Je précise que j'ai 2 feuilles :
"BDD" avec mon tableau de données donc la colonne "NOM" nommée en plage "bd_nom"
"Consultation" avec le format de contrôle pour la liste déroulante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Private Sub UserForm_Initialize()
Dim temp()
temp = Range("bd_nom") 'liste tableau temp(1 to n,1 To 1)ou temp =([E2],[E2].End(xlDown))
Call tri(temp, 1, UBound(temp, 1))
Me.ListBox1.List = temp
End Sub
 
 Sub tri(a(), gauc, droi) 'Quick sort
 ref = a((gauc + droi) \ 2, 1)
  g = gauc: d = droi
  Do
    Do While a(g, 1) < ref: g = g + 1: Loop
    Do While ref < a(d, 1): d = d - 1: Loop
      If g <= d Then
         temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
         g = g + 1: d = d - 1
      End If
   Loop While g <= d
   If g < droi Then Call tri(a, g, droi)
   If gauc < d Then Call tri(a, gauc, d)
End Sub

Voilà, si quelqu'un peut m'aider, s'il vois plait ,
Merci.

**tsgri** · 21/08/2009, 11h47

Bonjour,

J'ai beau chercher, essayer, je n'y arrive pas, alors je joint un fichier d'exemple pour illustrer mon probleme de : liste en désordre car impactée selon l'ordre le tri fait dans la BDD.

Merci.

**tsgri** · 21/08/2009, 19h04

Problème résolu avec une autre solution en passant par un controle active x et non controle de formulaire.
Merci.

Liste déroulante dans le désordre [XL-2007]

Excel

Discussions similaires

Partager

Partager