Excel, cellule avec liste pouvant varier.

Résolu
Bonjour,

Je suis en train de travailler sur la mise au point d'un petit fichier Excel qui me permettra de suivre, enfin je l'espère, l'état de mes stocks.
Je bloque depuis un petit moment sur un point et j'aimerais bien avoir un p'tit coup de pouce ;-)

Alors voilà, dans une de mes feuilles appelé Total Général, j'ai plusieurs colonnes.
Dans la colonne A, j'ai une description du produit.
Dans la colonne C, je voudrait que s'affiche sous forme de liste tous les ITN disponible pour cet article, qui se trouvent sur une autre feuille appelé Général ITN. (Grossièrement un ITN c'est comme un numéro de lot, un numéro de Batch...)

Dans la feuille général lot, j'ai donc:
En colonne A, j'ai le numéro d'ITN.
En colonne B, la description du produit.

Je voudrais donc que dans ma première feuille, en colonne C, en fonction de la description de l'article en colonne A, une liste soit créer en recherchant dans la deuxième feuille, tous les ITN correspondant à cette même description.
La liste ne doit pas non plus être figé car des ITN en feuille 2 peuvent se rajouter à tout moment...

J'ai déjà essayé des SI, INDIRECT, RECHERCHE... Mais rien à faire, je n'y arrive pas :-(

Quelqu'un peut il m'aider sur ce point?

Amicalement.

Yoann

14 réponses

  • Votes
  • Date
  • Date décroissante
Résumé de la discussion

Un problème Excel consiste à afficher dynamiquement, dans la colonne C de Total Général, tous les ITN correspondant à la description d'article en colonne A, en puisant dans une autre feuille répertoriant ITN et descriptions. Les solutions envisagées incluent VBA et des fonctions personnalisées pour concaténer les ITN, le recalcul à l’activation de la feuille et des listes déroulantes filtrant les ITN selon le stock. Des ajustements excluent les ITN à stock nul et actualisent les listes à l’activation, afin que les ajouts en source soient pris en compte. Des tests sur des jeux de données plus importants et des retours sur performance permettent d’évaluer l’impact du recalcul et des modifications sur l’expérience utilisateur.

Bobot (l’IA à votre service)
  1. Contributeur
    Essaie alors cette nouvelle version.

    J'ai enlevé la fonction de la feuille, mais faut la laisser dans le code, elle sert toujours (ajoute private devant si tu veux qu'elle n'apparaisse plus dans les fonctions personnalisées).

    Je t'ai mis des listes déroulantes, elles sont toutes recalculées à l'activation de la feuille.
    - Si la valeur présélectionnée est toujours existante elle est conservée.
    - Si elle n'y est plus ou si la cellule était vide le 1er item est sélectionné.
    - Les cellules avec plusieurs choix réels sont en fond jaune pale, pas de liste si un seul élément.

    http://www.cijoint.fr/cjlink.php?file=cj201110/cijnDnVkmc.xls

    Regarde si c'est mieux...
    Le seul temps de calcul à prendre en compte est celui de l'activation de la feuille Total Général.
    Pour tout autre lenteur regarde du coté de tes formules et macros.

    eric
    1. C'est super!!!!! Exactement se que je voulais!!!!

      Tu vas peut être me trouver chiand, mais quitte à optimiser le truc à fond, c'est possible de ne pas afficher Inc. quand il n'y a rien?
    2. Contributeur
      ben si, tu mets une chaine vide dans le code
      remplacer "inc." par ""
    3. Si je fais ça, il me met un bug sur cette ligne

      If InStr(liste, Cells(lig, 3)) = 0 Or Cells(lig, 3) = "" Then Cells(lig, 3) = Split(liste, ",")(0)
    4. Contributeur
      oui, normal, désolé...
      remplace cette ligne par :
              If InStr(liste, Cells(lig, 3)) = 0 Or Cells(lig, 3) = "" Then
                  If Len(liste) = 0 Then
                      Cells(lig, 3).ClearContents
                  Else
                      Cells(lig, 3) = Split(liste, ",")(0)
                  End If
              End If


      eric
    5. Trop beau! nickel, merci beaucoup beaucoup beaucoup, je commençais à désespérer!
      encore merci.
  2. Contributeur
    Bonjour,

    Nouvelle version avec traitements des solutions.
    J'ai supposé qu'il fallait également stock >0

    J'ai également corrigé un bug qui n'était pas encore ressorti dans la fonction concatITN (du coup si tu peux mettre un +1 à ce post qu'il soit plus visible).

    http://www.cijoint.fr/cjlink.php?file=cj201110/cijRw9Hqm1.xls
    Teste et contrôle sur des échantillons beaucoup plus larges.

    eric
    1. Contributeur
      Bonjour.

      Curieux que tu n'y sois pas arrivé ! C'est apparemment un problème classique, très fréquemment traité dans ce forum.
      Envoie ton fichier, qu'on puisse trouver où tu as fait l'erreur.

      Avec cjoint.com tu crées un lien, que tu copieras dans ton message.
      1. Ah bon? J'ai pourtant pas mal regardé mais je trouve rien.

        Je voudrais aussi éviter le VBA, tu penses que c'est possible? Créer une liste qui se baserait sur une recherche dans une feuille différente?
        1. Contributeur
          As-tu seulement consulté les SUGGESTIONS ci-dessus ?
        2. Oui mais je n'y ai rien trouvé :-(
          En même temps mon niveau de Excel n'est pas énorme se qui pourrait expliquer que je ne comprends peut être pas tout.

          Voilà un extrait du fichier: https://www.cjoint.com/?3JBv7YcFofu

          Désolé j'ai du supprimer pas mal de chose du fait qu'il contienne des infos sur ma boite ;-)
        3. Contributeur
          Bonjour,

          Je voudrais donc que dans ma première feuille, en colonne C, en fonction de la description de l'article en colonne A, une liste soit créer en recherchant dans la deuxième feuille, tous les ITN correspondant à cette même description.
          Une description unique peut donc correspondre à plusieurs ITN ?
          Une idée du maxi ou il n'y en a pas ?
          eric
        4. Oui justement... Et il n'y a pas de limite. Certaine description auront peut être 5 ITN dans les prochaines 5 années. D'autres en auront peut être des centaines....
        5. Contributeur
          à mon avis sans vba point de salut...
          eric
      2. Contributeur
        Salut.

        Tu as dû supprimer trop de choses, car je n'ai pas trouvé "tous les ITN disponible pour cet article" comme tu le mentionnes ici : "je voudrait que s'affiche sous forme de liste tous les ITN disponible pour cet article, qui se trouvent sur une autre feuille appelé Général ITN" ; en effet cette feuille contient un ITN différent pour chaque article !
        1. Un ITN peut correspondre qu'à une description, mais une description peut correspondre à plusieurs ITN.
          J'ai limité le nombre d'ITN à 1000 volontairement, car quand on en arrivera à 1000 je pense que je serais plus dans la boite ;-)
          1. Et normal que tu trouves pas tous les ITN, ceux ci viennent s'incrémenter au fur et à mesure de l'année...
        2. Tu as besoin d'une version un peu moins censuré?^^
          1. Oui eriiic, j'y ai répondu.

            Mais comme je disais je suis débutant en excel, et encore plus dans le VBA. Je me sens pas capable de réalisé ça :-(
          2. alors? Une idée?
        3. Contributeur
          yo_bref,

          pour répondre à ton post #10, je voudrais simplement une feuille "Général ITN" dans laquelle on trouve plusieurs ITN pour une même description.

          Dans la feuille "Total Général" la formule en B est correcte ; en C il faudra insérer une liste déroulante de cellule, dont la source sera issue d'un tableau à confectionner à partir des colonnes A et B de "Général ITN", et qui fera appel à la fonction INDIRECT.

          La difficulté sera de trouver une formule (nécessairement compliquée) pour adapter ce tableau aux mises à jour des ITN ; et c'est là qu'une macro apparaîtra peut-être nécessaire ...

          Cordialement.
          1. J'ai oublié de préciser, j'espère qu'il est pas trop tard... Il faudrait que dans la liste déroulante ne s'affiche pas les ITN qui sont = ou < à 0. Vu que si c'est le cas, il n'y en a plus.
            Sorry...
            1. Contributeur
              Bonjour,

              Comme tu as déjà du vba dans ton classeur une proposition par fonction personnalisée.
              Ca s'utilise comme une fonction excel : insertion / fonction... catégorie personnalisée.
              syntaxe :
              =concatITN(A2) utilise le séparateur par défaut "-"
              ou
              =concatITN(A15;", ") force le séparateur à ", " (ou tout autre chaine)

              Une fonction personnalisée doit être dans un module général (j'ai récupéré le module 2 vide, renommé 'fonctions_perso')

              Je n'ai pas voulu la mettre volatile car à-priori non nécessaire.
              Si tu as des problèmes de mise à jour lors de modification dans d'autres feuilles soit fait F9, soit ajoute dans 'Total Général' :
              Private Sub Worksheet_Activate()
              Worksheets("Total Général").[C:C].Calculate
              End Sub
              La colonne C se recalculera à chaque activation de la feuille

              https://www.cjoint.com/?AJCjxfnetoC
              Teste dans tous les sens, c'est plus difficile de revenir dessus 5 jours plus tard...

              eric
              1. ça m'a l'air pas mal du tout. Je pense que je vais incrémenter en plus le recalcul à chaque activation de la feuille car sinon c'est un peu long...
                Dernière chose, au lieu de mettre les ITN à la suite, est il possible selon toi de créer une liste déroulante? Car se que je voudrais après, c'est une fois l'ITN choisi que ça me marque directement les quantité en stock et la date de péremption.
                Il faudrait aussi que les ITN qui s'affiche dans la liste soit que ceux dont les stocks dépasse 0. Car si l'ITN est à 0 c'est qu'il n'y en a plus en stock et donc pas besoin de l'avoir.
                Tu penses que c'est faisable?
                1. Contributeur
                  Je pense que je vais incrémenter en plus le recalcul à chaque activation de la feuille car sinon c'est un peu long...
                  Ben ça sera encore plus long à chaque activation de la feuille puisque tu forceras le recalcul, à ne faire que si tu constates que des mises à jour ne se font pas.
                  Et qu'est-ce que tu appelles long ??? Vue la technique utilisée le balayage pour récupérer les items devrait durer une fraction de seconde.
                  Ce n'était pas déjà long avant ?
                  Maintenant si tu fais un copier coller de 1000 valeurs il faut lui laisser le temps de faire. Estime le temps sur une saisie

                  Dernière chose, au lieu de mettre les ITN à la suite, est il possible selon toi de créer une liste déroulante? Car se que je voudrais après, c'est une fois l'ITN choisi que ça me marque directement les quantité en stock et la date de péremption.
                  Sans doute, mais ça fait autant de listes à créer que de descriptions, donc toutes à recalculer à chaque saisie, une usine à gaz. Là le temps de réponse va être long...
                  Utilise plutôt une colonne supplémentaire où tu saisis l'ITN désiré.

                  Il faudrait aussi que les ITN qui s'affiche dans la liste soit que ceux dont les stocks dépasse 0. Car si l'ITN est à 0 c'est qu'il n'y en a plus en stock et donc pas besoin de l'avoir.
                  remplace :
                  If c.Offset(0, -1) > 0 Then
                  par :
                  If c.Offset(0, -1) > 0 And c.Offset(0, 3) > 0 Then

                  eric
                2. lorsque je fais une saisie en entrée ITN, ça mais au moins 10min à s'actualiser, la touche F9 ne fait pas grand chose de plus et lorsque la page recalcul enfin, ça plante le fichier :-(
                3. Contributeur
                  J'ai du mal à croire que ce soit seulement la fonction qui prenne toutes ces ressources...
                  On est bien d'accord que tu n'as pas activé le Private Sub Worksheet_Activate() ?

                  Sinon il va falloir partir sur un sub à lancer lorsque tu voudras une réactualisation.

                  eric

                  PS: tu as quoi comme version excel, processeur et taille de mémoire ?
                4. Non j'ai pas activé la fonction :-(
                  Regarde le fichier joint, c'est une version presque complète...
                  http://www.cijoint.fr/cjlink.php?file=cj201110/cijxU7lgXq.xls

                  La version excel: 2003
                5. Contributeur
                  Pas eu de pb dessus...
                  Si je revalide une fonction excel me rend la main tout de suite, pareil si je recalcule toute la feuille par F9

                  J'avais ajouté un PS :
                  PS: tu as quoi comme version excel, processeur et taille de mémoire ?
              2. Merci beaucoup Eric.

                Je suis en week end de 4 jours actuellement. Vive les ponts^^
                Je test donc ça mercredi et je te dis.

                En tout cas merci pour tout.

                Yoann