Microsoft 365 Challenge : rechercher plusieurs valeurs (plage) dans plusieurs feuilles et les reporter dans une feuille synthèse (selon une référence)

Bastien43 · 5 Mai 2022

Bonjour,

Je transmets le fichier en pièce jointe. Je peux travailler avec 365

Je cherche à récupérer les valeurs "Total Mandaté" Colonne M95 à M99, de chaque feuille OPE VILLE 1, 2, 3 (et plus) dans un tableau de synthèse (feuille "Gestion crédits 2021-2027") et plus exactement en colonne N de cette feuille de synthèse. Je joins le fichier avec des données en exemple.

Attention, les valeurs récupérées peuvent se situer sur des lignes plus basses ou plus hautes colonne M des feuilles OPE VILLE 1, 2, 3 (voir chaque feuille en exemple).

Le lien entre la feuille de synthèse et chaque feuille Opération est le numéro d'opération (cellule B6 feuille OPE Ville toujours fixe) et ce numéro apparaît en colonne A de la synthèse.
Dans la synthèse, les noms des opérations ne sont pas forcément dans l'ordre des feuilles.

Les noms des feuilles OPE VILLE sont quelconques, pas de structure en particulier.

Comment faire pour récupérer les valeurs dans la synthèse svp ? en clic idéalement, avec fonction ou macro ?

Merci pour votre aide
Cordialement

JHA · 5 Mai 2022

Bonjour à tous

A essayer

VB:

=INDEX(INDIRECT("'"&DECALER($E$21;EQUIV(RECHERCHE("zzz";$A$2:$A2);$E$22:$E$24;0);-1)&"'!M:M");EQUIV($L3;INDIRECT("'"&DECALER($E$21;EQUIV(RECHERCHE("zzz";$A$2:$A2);$E$22:$E$24;0);-1)&"'!L:L");0))

Validation matricielle (Ctrl+Maj+Entrée)

JHA

JHA · 5 Mai 2022

Bonjour à tous,

Peut-être un début de piste avec la fonction indirect()

VB:

=INDEX(INDIRECT("'OPE VILLE "&DROITE(RECHERCHE("zzz";$A$2:$A2);1)&"'!M:M");EQUIV($L3;INDIRECT("'OPE VILLE "&DROITE(RECHERCHE("zzz";$A$2:$A2);1)&"'!L:L");0))

JHA

Bastien43 · 5 Mai 2022

Bonjour,

Merci cela pourrait fonctionner. Cependant dans mon fichier avec les vraies données, les noms des onglets sont "quelconques" et ne sont pas lié au numéro d'opération (impossible de relier 1 2 et 3).

Et le numéro d'opération est aussi quelconque mais unique.

Par contre en B6 de chaque feuille, j'ai bien le même numéro d'opération.

Merci

JHA · 5 Mai 2022

Bonjours à tous,

Peux-tu faire une table de correspondance pour retrouver le nom de l'onglet.
exemple 1=toto, 2=tata, 3=titi, etc..

JHA

Bastien43 · 5 Mai 2022

JHA à dit:
Bonjours à tous,

Peux-tu faire une table de correspondance pour retrouver le nom de l'onglet.
exemple 1=toto, 2=tata, 3=titi, etc..

JHA

oui je pourrais je pense.

Comment cela se présenterait dans la formule indirect ?

Bastien43 · 5 Mai 2022

Eventuellement je peux aussi mettre le numéro d'opération dans le nom de la feuille (le nom de l'onglet sera long...)

Mais quelle est l'expression exacte ?

VB:

=INDEX(INDIRECT("' "&DROITE(RECHERCHE("zzz";$A$2:$A2);9)&"'!M:M");EQUIV($L3;INDIRECT("' "&DROITE(RECHERCHE("zzz";$A$2:$A2);9)&"'!L:L");0))

Ce code compte une erreur

L'idée de la table de correspondance semble mieux.
Voici le fichier avec la table en pièce jointe

JHA · 5 Mai 2022

Bonjour à tous,

Peut-être un truc du genre

VB:

=INDEX(INDIRECT("'"&DECALER($D$21;EQUIV(DROITE(RECHERCHE("zzz";$A$2:$A2);2);$D$22:$D$24;0);1)&"'!M:M");EQUIV($L3;INDIRECT("'"&DECALER($D$21;EQUIV(DROITE(RECHERCHE("zzz";$A$2:$A2);2);$D$22:$D$24;0);1)&"'!L:L");0))

JHA

Bastien43 · 5 Mai 2022

Merci,

Svp est-il possible de m'aider à adapter la formule selon ces données ? (voir tableau joint)

J'ai mis ce à quoi ressemble les noms des onglets... Désolé de prévenir que maintenant

Merci beaucoup pour votre aide

JHA · 5 Mai 2022

Bonjour à tous

A essayer

VB:

=INDEX(INDIRECT("'"&DECALER($E$21;EQUIV(RECHERCHE("zzz";$A$2:$A2);$E$22:$E$24;0);-1)&"'!M:M");EQUIV($L3;INDIRECT("'"&DECALER($E$21;EQUIV(RECHERCHE("zzz";$A$2:$A2);$E$22:$E$24;0);-1)&"'!L:L");0))

Validation matricielle (Ctrl+Maj+Entrée)

JHA

Bastien43 · 5 Mai 2022

JHA à dit:
Bonjour à tous

A essayer

VB:

=INDEX(INDIRECT("'"&DECALER($E$21;EQUIV(RECHERCHE("zzz";$A$2:$A2);$E$22:$E$24;0);-1)&"'!M:M");EQUIV($L3;INDIRECT("'"&DECALER($E$21;EQUIV(RECHERCHE("zzz";$A$2:$A2);$E$22:$E$24;0);-1)&"'!L:L");0))

Validation matricielle (Ctrl+Maj+Entrée)

JHA

Cela fonctionne
Merci beaucoup pour votre aide. Ca me dépanne grandement.
Bonne soirée

job75 · 5 Mai 2022

Bonsoir Bastien43, JHA,

Voyez le fichier joint et cette macro dans le code de la 1ère feuille :

VB:

Private Sub Worksheet_Activate()
Dim d As Object, w As Worksheet, nlig&, i&, j&, v As Variant
Set d = CreateObject("Scripting.Dictionary")
'---listes des numéros et feuilles sans doublons---
For Each w In Worksheets
    If w.Name <> Me.Name Then Set d(w.Range("B6").Value) = w 'mémorise la feuille
Next w
nlig = 6 'nombre de lignes des sous-tableaux
Application.ScreenUpdating = False
With [A1].CurrentRegion
    For i = 2 To .Rows.Count
        With .Cells(i, 1)
            If .Value <> "" Then
                Set w = d(.Value)
                For j = 2 To nlig
                    v = Application.VLookup(Trim(.Cells(j, 12)), w.Columns("A:M"), 13, 0)
                    If IsError(v) Then .Cells(j, 14) = "" Else .Cells(j, 14) = v
                Next j
            End If
        End With
    Next i
End With
End Sub

Elle se déclenche automatiquement quand on active la feuille.

A+

Bastien43 · 6 Mai 2022

job75 à dit:

Bonsoir Bastien43, JHA,

Voyez le fichier joint et cette macro dans le code de la 1ère feuille :

VB:

Private Sub Worksheet_Activate()
Dim d As Object, w As Worksheet, nlig&, i&, j&, v As Variant
Set d = CreateObject("Scripting.Dictionary")
'---listes des numéros et feuilles sans doublons---
For Each w In Worksheets
    If w.Name <> Me.Name Then Set d(w.Range("B6").Value) = w 'mémorise la feuille
Next w
nlig = 6 'nombre de lignes des sous-tableaux
Application.ScreenUpdating = False
With [A1].CurrentRegion
    For i = 2 To .Rows.Count
        With .Cells(i, 1)
            If .Value <> "" Then
                Set w = d(.Value)
                For j = 2 To nlig
                    v = Application.VLookup(Trim(.Cells(j, 12)), w.Columns("A:M"), 13, 0)
                    If IsError(v) Then .Cells(j, 14) = "" Else .Cells(j, 14) = v
                Next j
            End If
        End With
    Next i
End With
End Sub

Elle se déclenche automatiquement quand on active la feuille.

A+

Bonjour

Merci c'est super, la macro fonctionne, c'est top.

Merci à tous pour l'apport des 2 solutions (formule et macro)

Bonne journée,

Cordialement

Bastien43 · 6 Mai 2022

job75 à dit:

Bonsoir Bastien43, JHA,

Voyez le fichier joint et cette macro dans le code de la 1ère feuille :

VB:

Private Sub Worksheet_Activate()
Dim d As Object, w As Worksheet, nlig&, i&, j&, v As Variant
Set d = CreateObject("Scripting.Dictionary")
'---listes des numéros et feuilles sans doublons---
For Each w In Worksheets
    If w.Name <> Me.Name Then Set d(w.Range("B6").Value) = w 'mémorise la feuille
Next w
nlig = 6 'nombre de lignes des sous-tableaux
Application.ScreenUpdating = False
With [A1].CurrentRegion
    For i = 2 To .Rows.Count
        With .Cells(i, 1)
            If .Value <> "" Then
                Set w = d(.Value)
                For j = 2 To nlig
                    v = Application.VLookup(Trim(.Cells(j, 12)), w.Columns("A:M"), 13, 0)
                    If IsError(v) Then .Cells(j, 14) = "" Else .Cells(j, 14) = v
                Next j
            End If
        End With
    Next i
End With
End Sub

Elle se déclenche automatiquement quand on active la feuille.

A+

Rebonjour,

Si je souhaite récupérer la synthèse de la colonne H de chaque feuille en colonne 14 (N) et la colonne M de chaque feuille en colonne 17 (Q), comment modifier le code svp ?

C'est-à-dire que je souhaite récupérer les valeurs de 2 colonnes.

J'ai essayé de copier la macro en dessous, ajouter une nouvelle variable mais cela ne fonctionne pas.

Merci pour votre aide

job75 · 6 Mai 2022

Bonjour Bastien43,

Colonne H ? Il n'y a rien... Mais bon modifiez la boucle j comme suit :

VB:

                For j = 2 To nlig
                    v = Application.Match(Trim(.Cells(j, 12)), w.Columns("A"), 0)
                    If IsError(v) Then
                        .Cells(j, 14) = ""
                        .Cells(j, 17) = ""
                    Else
                        .Cells(j, 14) = w.Cells(v, "H")
                        .Cells(j, 17) = w.Cells(v, "M")
                    End If
                Next j

A+

Bastien43 · 6 Mai 2022

Merci,
Cela fonctionne sur le fichier transmis.
Une question, après insertion dans mon fichier, avec toutes les feuilles, voici le résultat :

J'ai un bug sur cette ligne

VB:

Set w = d(.Value)

L'espion me dit, "expression non définie dans le contexte".

Quelle serait l'origine de l'erreur svp ? J'ai bien vérifié position des colonnes, la cellule B6...

Merci

Microsoft 365 Challenge : rechercher plusieurs valeurs (plage) dans plusieurs feuilles et les reporter dans une feuille synthèse (selon une référence)

Bastien43

XLDnaute Occasionnel

JHA

JHA

XLDnaute Barbatruc

Bastien43

XLDnaute Occasionnel

JHA

XLDnaute Barbatruc

Bastien43

XLDnaute Occasionnel

Bastien43

XLDnaute Occasionnel

JHA

XLDnaute Barbatruc

Bastien43

XLDnaute Occasionnel

Pièces jointes

JHA

XLDnaute Barbatruc

Pièces jointes

Bastien43

XLDnaute Occasionnel

job75

XLDnaute Barbatruc

Pièces jointes

Bastien43

XLDnaute Occasionnel

Bastien43

XLDnaute Occasionnel

job75

XLDnaute Barbatruc

Bastien43

XLDnaute Occasionnel

Discussions similaires