Créer une formule avec un nombre variable de sources

Version imprimable

15/06/2020, 16h09
Soleil_Vert

Créer une formule avec un nombre variable de sources
Bonjour,

Je vous prie de m'excuser si l'intitulé vous a perdu, j'ai moi-même du mal à nommer ce que je cherche à faire (la faute je pense à des lacunes en math et en vba), et à cause de ça, j'ai beau chercher des heures, c'est sans succès que je ne trouve une solution correspondant à mon projet. Me voilà donc sur ce forum, à la recherche de savoir.

Je vous expose ma problématique de manière concrète :
J'ai une feuille que l'on nommera "GLOBAL".
J'ai un nombre variable de feuilles en plus de "GLOBAL" (numérotés 1 à 100).
Je souhaite que la cellule A1 de l'onglet "GLOBAL" contienne la somme des cellules A1 des autres onglets (la valeur des cellules A1 des autres onglets peut être modifié manuellement, du coup j'ai besoin d'une formule ce qui me permettra d'avoir la cellule A1 de GLOBAL toujours à jour).

Ma logique serait de positionner dans A1 de GLOBAL une formule du type =Feuil[n]!A1 + Feuil[n+1]!A1 + Feuil[n+2]!A1...etc
Par exemple pour 2 feuilles, la formule serait =Feuil1!A1 + Feuil2!A1
Pour 4 feuilles, la formule serait =Feuil1!A1 + Feuil2!A1 + Feuil3!A1 + Feuil4!A1

Problème, je ne vois pas du tout comment faire pour trouver une solution simple. Je ne sais même pas comment nommer ça correctement...

La seule solution que j'ai trouvé me semble un peu tarabiscotée :
Dans une 1ère étape je récupère la variable représentée par le nombre de feuilles (exemple : f = Sheets.Count -1)
Ensuite j'utilise la variable dans un For (exemple : For a = 1 to f)
Dans le For je positionne la valeur de la feuille a dans la cellule qui se trouve à la ligne 1 colonne a+1 ( Sheets("GLOBAL").Range("A"&a+1) = "=" & a &"!A1" )
Puis je termine en positionnant dans A1 de GLOBAL la formule qui va additionner les cellules ( Sheets("GLOBAL").Range("A1") = "=SUM(A2:A" & f+1)" )
Code:

1 2 3 4 5 6 f = Sheets.Count -1 For a = 1 to f Sheets("GLOBAL").Range("A"&a+1) = "=" & a &"!A1" next Sheets("GLOBAL").Range("A1") = "=SUM(A2:A" & f+1)"
De cette manière ça fonctionne, mais je n'aime pas devoir passer par des cellules intermédiaires. Vous avez des idées ?
15/06/2020, 16h27
BENNASR

1 pièce(s) jointe(s)

bonjour
sans VBA :

Citation:

=SOMME(Feuil1:Feuil5!A1)

pour faire un choix de l'onglet et l'onglet de départ Mr boisgontierjacques propose :

Bonjour,

Essayez de construire votre formule dynamiquement.

Un peu comme ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
Function Formule(Wb As Workbook) As String
Dim Ws As Worksheet, f As String
    For Each Ws In Wb.Worksheets
        If Ws.Name <> "Global" Then 'si le nom n'est pas "global"
            f = f & Ws.Name & "!A1+"
        End If
    Next
    Formule = "=" & Left$(f, Len(f) - 1)
End Function
 
Sub AppelFonction()
    Sheets("Global").Range("A1").Formula = Formule(ThisWorkbook)
End Sub

16/06/2020, 07h58
Soleil_Vert

Citation:

Envoyé par BENNASR

bonjour
sans VBA :
pour faire un choix de l'onglet et l'onglet de départ Mr boisgontierjacques propose :

Bonjour BENNASR, merci pour votre réponse.
Je ne connaissais pas cette possibilité alors que ça me semble être quelque chose de très basique.
Ça pourrait être une première solution, à condition de s'assurer que les onglets ne seront pas déplacés, sinon il y a un risque d'exclusion de certaines données.

Citation:

Envoyé par pijaku

Bonjour,

Essayez de construire votre formule dynamiquement.

Un peu comme ceci :

Bonjour pijaku, merci également pour votre réponse.
Je n'avais pas du tout pensé à positionner dans une boucle la définition d'une formule qui reprendrait sa propre définition de la boucle précédente (f). Mais ça semble une évidence maintenant et je me sens tout con.
Je vais tester cela de ce pas, je vous tiens informés

C'est parfait, j'ai simplement ajouté la formule à ma boucle existante (après quelques modifications en amont) puis j'ai fait appliquer la formule dans un tableau (puisqu'il n'y avait pas, bien entendu, qu'une seule cellule à récupérer, c'était juste pour l'exemple).

Je vais pas mettre tout le code de ma macro, mais pour le sujet qui nous intéresse ici, j'ai fait en gros :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 c = Feuil6.Range("B" & b).Value 'date1 e = Feuil6.Range("B" & d).Value 'date2 f = WorksheetFunction.RoundUp(((e - c) + 1) / 7, 0) 'nb de semaines For i = 1 To f tt = tt & Sheets("" & i & "").Name & "!R3+" Next Sheets("TABLEAU_DE_SUIVI").Activate Sheets("TABLEAU_DE_SUIVI").Cells(3, 2) = "=" & Left(tt, Len(tt) - 1) Sheets("TABLEAU_DE_SUIVI").Range(Cells(3, 2), Cells(a + 2, x + 4)) = Sheets("TABLEAU_DE_SUIVI").Cells(3, 2).Formula
Merci encore !