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
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
 
Dernière édition:
Solution
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

XLDnaute Barbatruc
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

XLDnaute Occasionnel
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
 

Bastien43

XLDnaute Occasionnel
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
 
Dernière édition:

JHA

XLDnaute Barbatruc
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

XLDnaute Occasionnel
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
 

Pièces jointes

  • Test - Copie (1) (1).xlsx
    653.1 KB · Affichages: 2

JHA

XLDnaute Barbatruc
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
 

Pièces jointes

  • Test - Copie (1) (1).xlsx
    650 KB · Affichages: 8

job75

XLDnaute Barbatruc
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+
 

Pièces jointes

  • Test - Copie(1).xlsm
    686.9 KB · Affichages: 9

Bastien43

XLDnaute Occasionnel
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

XLDnaute Occasionnel
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
 
Dernière édition:

job75

XLDnaute Barbatruc
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

XLDnaute Occasionnel
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
 

Discussions similaires