Microsoft 365 comment rendre dynamique une feuille synthese qui somme les memes cellules de feuilles differentes

[Taristrou]

Bonjour,

J'ai besoin de rendre dynamique ma feuille de synthèse qui somme les valeurs des mêmes cellules des feuilles suivantes.

Chaque feuille de données est nommée sur 4 caractères alphanumériques. Il peut y avoir jusqu'à une trentaine de feuilles différentes. Toutes les feuilles ont une structure identique.
La feuille de synthèse somme les valeurs d'une cellule sur toutes les feuilles en utilisant par exemple la formule =SOMME(MORZ:NAPO!B3) pour faire la somme des valeurs en B3 de toutes les feuilles entre la feuille MORZ et la feuille NAPO.

Pour rendre la sélection des feuilles dynamiques, une feuille Select permet de choisir la première feuille (MORZ par exemple) et la dernière feuille de l'analyse (NAPO par exemple).

Je n'arrive plus à obtenir les données quand je tente d'intégrer ces saisies. Je me suis inspirée d'autres posts du forum sur le sujet, mais je n'arrive à rien ...
Dans le fichier joint, j'ai mis uniquement 6 feuilles de données pour tester.

Merci pour votre aide !

 

[sylvanu]

Bonjour Taristrou, et bienvenu sur XLD,
Après moult essais je suis arrivé à la conclusion que Indirect est incompatible du multipages.
Peut être est ce une erreur d'appréciation et que quelqu'un vous trouvera une solution.
Sinon, êtes vous ouvert au VBA ?

 

[JHA]

Bonjour à tous,

Une alternative avec decaler et indirect pour le total des mois.

JHA

 

[Taristrou]

Bonjour Taristrou, et bienvenu sur XLD,
Après moult essais je suis arrivé à la conclusion que Indirect est incompatible du multipages.
Peut être est ce une erreur d'appréciation et que quelqu'un vous trouvera une solution.
Sinon, êtes vous ouvert au VBA ?

Bonjour Sylvanu,

Je ne suis pas contre le VBA, mais mon niveau n'est pas terrible ...
Seule, je préfère utiliser Excel. Cela m'évite une migraine en VBA
Vous auriez une idée ?

Merci !

 

[Taristrou]

Bonjour à tous,

Une alternative avec decaler et indirect pour le total des mois.

JHA

Merci, mais cela ne correspond pas à ma demande.
Je ne veux pas que l'utilisateur soit obligé d'aller modifier toutes les colonnes et toutes les lignes de la feuille synthèse ...
J'ai mis juste une sélection dans le tableau, mais il y a normalement beaucoup plus de données à traiter.

 

[sylvanu]

Vous auriez une idée ?

Perso, non.
Alors au cas où, une PJ avec cette macro :

Sub Worksheet_Activate()
    Dim Début$, Fin$, F, GoDeb%, GoFin%
    Sheets("Rapport").Range("B2:AV32").ClearContents ' on efface le tableau existant
    Application.ScreenUpdating = False
    Début = Sheets("Select").Range("B2")            ' feuille de début
    Fin = Sheets("Select").Range("B3")              ' feuille de fin
    For Each F In Worksheets                        ' on supprime les "-" car texte donc génère des erreurs
        Sheets(F.Name).Range("B2:AV32").Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
    Next F
    GoDeb = 0: GoFin = 1
    For Each F In Worksheets
        If F.Name = Début Then GoDeb = 1            ' si feuille debut atteinte
        If GoDeb + GoFin = 2 Then                   ' si les deux flags valent 1 on additionne
            Formule = Formule & "+" & F.Name & "!B2"    ' on construit la formule
        End If
        If F.Name = Fin Then GoFin = 0              ' si feuille fin atteinte
    Next F
    [B2:AV32].FormulaLocal = "=" & Mid(Formule, 2)  ' on colle la formule
    [F2:F32].FormulaLocal = "=D2/C2"                ' on colle la formule calcul %
    [B2:AV32] = [B2:AV32].Value                     ' on remplace par les valeurs
End Sub

1- La macro s'active automatiquement lorsqu'on sélectionne la feuille "Rapport"
2- En colonne F je fais le ration colD/colC
3- Dans les feuilles j'ai supprimé les "-" qui sont du texte et génère des erreurs en VBA dans les formules.

 

[Hasco]

Bonjour,

Voici une proposition par requête power query et TCD

Lorsque les données ou la sélection De A changent, enregistrer le classeur puis actualiser tout (Données/Actualiser tout)

Comme il n'y a pas de tableau structuré dans le fichier, la requête l'interroge comme un fichier extérieur, c'est pourquoi j'ai rajouté la variable "Fichier" dans le tableau (devenu structuré) de sélections, vérifier sa valeur avant de rafraîchir la requête ou le tcd

Pour certaines colonnes on ne sait pas s'il s'agit de valeurs monétaires ou unitaires à vérifier.

 

[ALS35]

Bonjour à tous,
Une proposition par formule (pas trop simple!!) en B3, avec la liste des feuilles existantes et le choix en Feuille Select. Cette formule peut se recopier vers le bas et vers la droite, à tester.
Cordialement

 

[Taristrou]

Perso, non.
Alors au cas où, une PJ avec cette macro :

Sub Worksheet_Activate()
    Dim Début$, Fin$, F, GoDeb%, GoFin%
    Sheets("Rapport").Range("B2:AV32").ClearContents ' on efface le tableau existant
    Application.ScreenUpdating = False
    Début = Sheets("Select").Range("B2")            ' feuille de début
    Fin = Sheets("Select").Range("B3")              ' feuille de fin
    For Each F In Worksheets                        ' on supprime les "-" car texte donc génère des erreurs
        Sheets(F.Name).Range("B2:AV32").Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
    Next F
    GoDeb = 0: GoFin = 1
    For Each F In Worksheets
        If F.Name = Début Then GoDeb = 1            ' si feuille debut atteinte
        If GoDeb + GoFin = 2 Then                   ' si les deux flags valent 1 on additionne
            Formule = Formule & "+" & F.Name & "!B2"    ' on construit la formule
        End If
        If F.Name = Fin Then GoFin = 0              ' si feuille fin atteinte
    Next F
    [B2:AV32].FormulaLocal = "=" & Mid(Formule, 2)  ' on colle la formule
    [F2:F32].FormulaLocal = "=D2/C2"                ' on colle la formule calcul %
    [B2:AV32] = [B2:AV32].Value                     ' on remplace par les valeurs
End Sub

1- La macro s'active automatiquement lorsqu'on sélectionne la feuille "Rapport"
2- En colonne F je fais le ration colD/colC
3- Dans les feuilles j'ai supprimé les "-" qui sont du texte et génère des erreurs en VBA dans les formules.

Bonjour Sylvanu,

Merci pour le code ! Je vais tester.

Cordialement

 

[Taristrou]

Bonjour,

Voici une proposition par requête power query et TCD

Lorsque les données ou la sélection De A changent, enregistrer le classeur puis actualiser tout (Données/Actualiser tout)

Comme il n'y a pas de tableau structuré dans le fichier, la requête l'interroge comme un fichier extérieur, c'est pourquoi j'ai rajouté la variable "Fichier" dans le tableau (devenu structuré) de sélections, vérifier sa valeur avant de rafraîchir la requête ou le tcd

Pour certaines colonnes on ne sait pas s'il s'agit de valeurs monétaires ou unitaires à vérifier.

Bonjour Hasco,

Cela fonctionne, et c'est magique pour moi !
J'en demande beaucoup ... mais si vous avez 5 minutes, si vous pouviez m'expliquer rapidement les différentes étapes, en particulier dans Power Query, cela pourra m'aider à réadapter cela à mon fichier final.

Merci beaucoup !
Cordialement,

 

[Taristrou]

Bonjour à tous,
Une proposition par formule (pas trop simple!!) en B3, avec la liste des feuilles existantes et le choix en Feuille Select. Cette formule peut se recopier vers le bas et vers la droite, à tester.
Cordialement

Bonjour ALS35,

Merci infiniment, cela fonctionne parfaitement !
C'est votre solution que j'adopte pour le moment .

Cordialement,

 

[Hasco]

Bonjour Hasco,

Cela fonctionne, et c'est magique pour moi !
J'en demande beaucoup ... mais si vous avez 5 minutes, si vous pouviez m'expliquer rapidement les différentes étapes, en particulier dans Power Query, cela pourra m'aider à réadapter cela à mon fichier final.

Merci beaucoup !
Cordialement,

Bonjour,
Dès que j'aurai un peu de temps, je vous répondrai plus longuement. Je ne suis ici qu'en "pointillé" ces jours ci
Bonne journée

 

[ALS35]

Bonjour,
Alors une version 2 en déportant plus intelligemment les calculs de plages dans la feuille Select.
Cordialement

 

[job75]

Bonjour Taristrou, le forum,

Plusieurs remarques sur la formule utilisée par ALS35 au post #13 :

- le principe est celui utilisé par le regretté Jacques Boisgontier sur son site

- mais la formule ne fait aucune somme (chez moi sur Excel 2019), pour cela il faut ajouter la fonction N() qui a été oubliée

- on peut utiliser SOMMEPROD ce qui évite la validation matricielle sur les versions autres que MS 365.

Voyez le fichier joint qui traite les colonnes A à E, pour les colonnes suivantes il faut dans les feuilles :

- remplacer le point par le séparateur décimal (la virgule) pour convertir les textes en nombres

- supprimer les "EUR".

A+

 

[Hasco]

Bonjour,

Voici une version avec une petite macro qui actualise la requête et le rapport lorsque vous aurez changé les valeurs des paramètres DE, A.

J'ai commenté les lignes de la requête.

Comme c'est un classeur avec macro venant du web (téléchargé) vous aurez peut-être à le débloquer avant de l'ouvrir pour faire tourner la macro : click-droit sur son nom dans l'explorateur de fichiers, puis 'propriétés' et enfin cocher la case 'débloquer' en bas de la fenêtre de propriétés. Validez.

Cordialement