Discussion :

[PENSEUR33]

Bonjour,

J'ai un projet à élaborer (et surtout terminer) pour mon travail.

Le principe :

- Un seul fichier Excel

- une feuille "CS" pour afficher les données issues du TCD

- une feuille des données

- une feuille contenant le TCD

Tout est en place (avec de l'aide) :

- Une liste déroulante créé un lien entre le TCD (avec un filtre de champ) et la feuille "CS"
- Une macro aide à faire fonctionner et permet de remplir les données dans la feuille "CS"

Le problème est le suivant :
Le filtre de la liste déroulante fonctionne mal : les données ne correspondent pas

J'ai placé un fichier (avec des noms fictifs)

Je n'ai plus de nouvelle de la personne qui avait commencé à m'aider, pouvez-vous m'aider svp?
Merci

[jerome.vaussenat]

salut PENSEUR33,

Je n'ai plus de nouvelle de la personne qui avait commencé à m'aider,

Ben c'est certainement qu'il bosse et qu'il n'a plus de temps à consacrer au forum ...

Mais comme il n'est pas tout seul ...

Voila, si j'ai bien compris ce que tu vous lais. J'ai fait une toute petite modification dans ton code.
Il affichait tout sauf ce que tu sélectionnais dans la liste. L'erreur venant des deux textes en rouge que j'ai inversé par rapport à ton code original.

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
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "L1" Then
        With Sheets("TCD").PivotTables(1).PivotFields("MLot")
        .ClearAllFilters
          For i = 1 To .PivotItems.Count
          x = .PivotItems(i).Name
            If .PivotItems(i).Name = Sheets("CSMK").Range("L1").Value Then
              .PivotItems(i).Visible = True
            Else
              .PivotItems(i).Visible = False
            End If
          Next i
    End With
End If
End Sub

En espérant que c'est bien ce que tu veux.

GG

[PENSEUR33]

Bonjour,

ouf!
Tu as tout compris!
Et en si peu de temps! (je suis dessus depuis???)

Ben c'est certainement qu'il bosse et qu'il n'a plus de temps à consacrer au forum ...

Nan pas vraiment, mais c'est surement de ma faute

En espérant que c'est bien ce que tu veux.

Ca marche!

Juste pour la suite et pour compléter cette feuille "CS" :

1. Mon tableau de données de travail contiendra beaucoup de lignes : de ce fait, la liste déroulante aura 14 choix possible (lot 1 au 14).
Il faut que fasse des tests avec cette modif (mortelle!!!)

2. Serait il possible de comptabiliser le nombre de lignes correspondantes au choix de la liste déroulante ?
ex:
lot 1 résultat 8 entreprises
lot 2 résultat 9 entreprises
etc....

3. Dernière requête, et si tu as le temps bien sur! (ce serait la cerise sur le gâteau)
La feuille "CS" est destinée a être produite dans le cadre de soldes de marchés
Le seul problème c'est les lignes blanches sans données.
Il faudrait trouver une solution pour éviter cela.


Mon moral viens de faire une flèche vers le haut!!!!

Merci encore

[jerome.vaussenat]

Salut,

Voici le fichier avec une proposition de solution (si j'ai bien compris ...)

Point 1.
Pour moi, tu n'as rien à faire si on ajoute des lots supplémentaires. Le code fait le boulot tout seul.
Je te propose tout de même une solution pour alimenter la liste déroulante selon le contenu de la feuille fusion. Pour obtenir cela, j'ai :

• Créée un TCD placé dans la feuille liste) qui permet d'obtenir la liste des lots (sans doublons)
• Basé ta liste déroulante sur ce TCD.

point 2
J'ai mis une petite formule (NBVAL) qui compte le nobre de lignes dans ton TCD. placée dans la cellule L2 de la feuille "CSMK".

Point 3
Ben il n'y aura pas de cerise sur le gâteau !

GG

[78chris]

Bonjour à tous

Plutôt qu'un TCD, et du VBA, j'utiliserai une requête PowerQuery

On garde juste une ligne de VBA pour actualiser la requête

[PENSEUR33]

Point 1.
Je te propose tout de même une solution pour alimenter la liste déroulante selon le contenu de la feuille fusion. Pour obtenir cela, j'ai :

• Créée un TCD placé dans la feuille liste) qui permet d'obtenir la liste des lots (sans doublons)
• Basé ta liste déroulante sur ce TCD.

point 2
J'ai mis une petite formule (NBVAL) qui compte le nobre de lignes dans ton TCD. placée dans la cellule L2 de la feuille "CSMK".
Point 3
Ben il n'y aura pas de cerise sur le gâteau !

Bonjour,

Merci! SAV ultra rapide
Super pour tes modifs et ajouts.

Dommage pour la 3
Juste pour savoir : trop complexe, je suppose...
Il y a t'il un quelconque espoir Docteur?

Une précision sur le Module1 :

Est-ce que ces lignes de codes sont obligatoires pour chaque que l'ensemble fonctionne ?
Si oui, il va falloir que j'écrive une macro pour chaque "lot"?

Code :

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

1
2
3
4
5
6
7
8
9
10
Sub Mselectmarche3()
'
' Sélection marché 3 Macro
'
 
'
    With ActiveSheet.PivotTables("TCDFusion").PivotFields("MLot").ClearAllFilters
        .PivotItems("13.0551.00 (Lot 3)").Visible = True
    End With
End Sub

Encore : un GRAND merci pour tout!

[PENSEUR33]

Bonjour à tous
Plutôt qu'un TCD, et du VBA, j'utiliserai une requête PowerQuery
On garde juste une ligne de VBA pour actualiser la requête

Bonjour,

Merci pour cette alternative!

PowerQuery est vraiment puissant.
Je l'ai utilisé pour rassembler des feuilles en une seule pour obtenir ma feuille de données : MEGA outil!

Juste des précisions :

1. Dans la feuille "CSMK" on garde seulement ces lignes VBA :

Code :

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

1
2
3
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [Choix]) Is Nothing Then ThisWorkbook.RefreshAll
End Sub

2. De même pour le module1 :
Il est obsolète?

Encore merci pour cette solution

[78chris]

Bonjour

Avec la solution PowerQuery, on n'utilise plus le TCD qui peut être supprimé et donc plus le code du module1 qui peut aussi être supprimé

Idem pour CMSK : le code lié au TCD n'a plus lieu d'être.

[PENSEUR33]

Re-bonjour,

Merci pour les confirmations.

J'ai des précisions au sujet du PQ.
En regardant la requête en détail :

- Tu as choisi simplement les colonnes : "Tiers", "année scolaire" et "Montant", seulement?

- Quand on choisi un lot dans la liste déroulante, quel est le lien entre "lot" et les données?

=> si tu as bien sur le temps, j'ai besoin de précisions svp...


Et pour terminer tu as modifié les cellules de la matrice de la feuille "CSMK"


Ce travail qui me prenait TROP de temps...
Je n'en reviens toujours pas d'avoir le choix possible avec ces deux solutions
Comme on dit : on apprend tous les jours... mais là j'ai fait une avancée énorme!

MEGA merci à vous deux!

[78chris]

Bonjour

J'ai filtré la requête à l'étape 3 en utilisant la cellule nommée Choix, correspondant à la liste déroulante du Mlot, dont on récupère la valeur par

Code :

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

Excel.CurrentWorkbook(){[Name="Choix"]}[Content][Column1]{0}

Ensuite on calcule la somme par entreprise via un regroupement

L'index a été ajouté pour correspondre à tes chiffres mais n'est pas indispensable

Oui j'ai simplifié les formules de CSMK

[PENSEUR33]

Merci pour ces précisions.

Désolé, mais j'ai TOTALEMENT laissé de côté ce travail (2 semaines)
Et je m'y remet doucement, donc 1 peu charrette....
Il me manque juste le départ
Comment as tu commencé cette requête, svp?


Oups
J'ai détecté un problème au niveau de la feuille "CSMK" ligne 15
A14 =SI(LIGNE()-7>NBVAL(TFusion_2[[#Tout];[Index]]);"";PQ!A8),
A15 =SI(LIGNE()-7>NBVAL(TFusion_2[[#Tout];[Index]]);"";PQ!A10),
=>le numéro 9 est répété deux fois


J'ai essayé de modifier en recopiant les fonctions à l'identique vers le bas, mais cela engendre des "#REF!"
svp, tu m'aider sur ce problème?

[78chris]

Re

Se placer dans une cellule quelconque du Tableau TFusion, Données, A partir d'un Tableau

[PENSEUR33]

Oups

J'ai détecté un problème au niveau de la feuille "CSMK" ligne 15

A14 =SI(LIGNE()-7>NBVAL(TFusion_2[[#Tout];[Index]]);"";PQ!A8),
A15 =SI(LIGNE()-7>NBVAL(TFusion_2[[#Tout];[Index]]);"";PQ!A10),
=>le numéro 9 est répété deux fois


J'ai essayé de modifier en recopiant les fonctions à l'identique vers le bas, mais cela engendre des "#REF!"
svp, tu m'aider sur ce problème?

[78chris]

RE

Effectivement on a parfois des effets de bord avec la relativisation dans les formules sur les tableaux structurés

J'ai mis de chiffres en dur dans la 1ère colonne et une MFC par masquer ceux en trop et je les utilise pour les autres formules, ce qui semble OK

[PENSEUR33]

Super pour ces modifs! Je ferais mes tests...

Par contre, je vais (encore) te demander des infos :

J'ai filtré la requête à l'étape 3 en utilisant la cellule nommée Choix, correspondant à la liste déroulante du Mlot, dont on récupère la valeur par
Excel.CurrentWorkbook(){[Name="Choix"]}[Content][Column1]{0}

Comment tu appliques ce filtre sur ton tableau de la requête?
Click droit sur l'en tête de la colonne et après?

Ensuite on calcule la somme par entreprise via un regroupement

Là je pense que c'est "Regrouper par"

Je refait pas à pas les étapes afin de pouvoir créer et réutiliser ces astuces

Merci de ta patience

[78chris]

Re

Comment tu appliques ce filtre sur ton tableau de la requête?

Tu filtres sur une date au hasard et ensuite tu remplaces dans la barre de formule #Date(...) par
Excel.CurrentWorkbook(){[Name="Choix"]}[Content][Column1]{0}

Là je pense que c'est "Regrouper par"

Oui

[PENSEUR33]

Re
Tu filtres sur une date au hasard et ensuite tu remplaces dans la barre de formule #Date(...) par
Excel.CurrentWorkbook(){[Name="Choix"]}[Content][Column1]{0}
Oui

J'ai pu recréer (enfin) ce filtre

Tu as remarqué je n'ose pas de demander des détails sur la fonction

Code :

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

Excel.CurrentWorkbook(){[Name="Choix"]}[Content][Column1]{0}

Je continu mon travail...

Encore mille merci...

[78chris]

RE

Excel.CurrentWorkbook(){[Name="Choix"]} : c'est ce qui correspond au nom (Name) Choix dans le classeur en cours

[Content][Column1]{0} : Content c'est son contenu considéré comme une table dont on récupère la 1ère (et unique colonne, baptisée Column1 automatiquement), et la 1ère ligne numérotée 0 par PowerQuery

donc tout ça pour récupérer le contenu de l’unique cellule nommée Choix

[PENSEUR33]

RE

Excel.CurrentWorkbook(){[Name="Choix"]} : c'est ce qui correspond au nom (Name) Choix dans le classeur en cours

[Content][Column1]{0} : Content c'est son contenu considéré comme une table dont on récupère la 1ère (et unique colonne, baptisée Column1 automatiquement), et la 1ère ligne numérotée 0 par PowerQuery

donc tout ça pour récupérer le contenu de l’unique cellule nommée Choix

Bonjour,

En effet, cela parait tellement logique

Merci, pour toutes ces réponses précises et rapides!

[PENSEUR33]

Bonjour,

Je suis aujourd'hui au bureau et je teste le fichier sur mon PC et sur le réseau partagé.

J'ai une requête au sujet des lignes de codes qui permettent de rafraichir la liste sur la feuille "CSMK".

Code :

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

1
2
3
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [Choix]) Is Nothing Then ThisWorkbook.RefreshAll
End Sub

Serait il possible de trouver une solution alternative sans passer par du code (VBA) pour effectuer cette fonction?

Le but est d'éviter que les utilisateurs valident ces messages de sécurité (tous les PC du bureau sont paramétrés avec ces restrictions).
Pièce jointe 573113
Pièce jointe 573110

Merci.