XL 2013 Liste des onglets sans formule

Airone784

XLDnaute Occasionnel
Bonjour à tous,

J'ai trouvé sur le site de Boisgontier (il est fort le monsieur) une formule qui me permet d'afficher le nom des feuilles excel de mon classeur.

-Créer un nom de champ NomsFeuilles
=STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

Pour obtenir les noms des feuilles d'un classeur verticalement:

=SI(LIGNES($1:1)<=NBVAL(NomsFeuilles);INDEX(NomsFeuilles;LIGNES($1:1));"")

Cette formule fonctionne très bien. Par contre, moi je voudrais obtenir la liste des onglets dont le nom commence par 01 par exemple.

Je n'arrive pas à trouver la solution.

Merci d'avance pour votre aide.
 

BOISGONTIER

XLDnaute Barbatruc
Repose en paix
Bonjour,


=SIERREUR(INDEX(NomsFeuilles;PETITE.VALEUR(SI(GAUCHE(NomsFeuilles;2)="01";EQUIV(NomsFeuilles;NomsFeuilles;0));LIGNES($1:1)));"")
valider avec maj+ctrl+entrée

Boisgontier
 

Pièces jointes

  • Copie de XL4Lire.xls
    51.5 KB · Affichages: 48

Airone784

XLDnaute Occasionnel
Bonjour,

La formule fonctionne très bien. Maintenant, je souhaite faire un somme.si.ens uniquement dans les feuilles dont le nom commence par 01 (01 étant le n° de semaine). Ensuite sur une autre ligne faire un somme.si pour les feuilles dont le nom commence par 02,...

J'ai essayé cette formule mais j'ai un #REF :
En effet dès que la feuille ne répond pas à la contrainte, j'ai une erreur...

=SOMMEPROD(SOMME.SI.ENS(INDIRECT("'"&SI(GAUCHE(nom_feuille;2)="01";nom_feuille)&"'!N7:N200");INDIRECT("'"&SI(GAUCHE(nom_feuille;2)="01";nom_feuille)&"'!M7:M200");BK$4;INDIRECT("'"&SI(GAUCHE(nom_feuille;2)="01";nom_feuille)&"'!a7:a200");$BJ6))
 
Dernière édition:

BOISGONTIER

XLDnaute Barbatruc
Repose en paix
Bonjour


Exemple en PJ:

Nom de champ
nf=STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")
nfx=DECALER(Recap!$G$2;;;NB.SI(Recap!$G$2:$G$18;"><"&""))

=SOMMEPROD(SOMME.SI.ENS(INDIRECT("'"&nfx&"'!b2:b10");INDIRECT("'"&nfx&"'!a2:a10");B2))

Boisgontier
 

Pièces jointes

  • Somme3DCondEns.xlsm
    23.5 KB · Affichages: 18
Dernière édition:

Airone784

XLDnaute Occasionnel
Bonjour


Exemple en PJ:

Nom de champ
nf=STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")
nfx=DECALER(Recap!$G$2;;;NB.SI(Recap!$G$2:$G$18;"><"&""))

=SOMMEPROD(SOMME.SI.ENS(INDIRECT("'"&nfx&"'!b2:b10");INDIRECT("'"&nfx&"'!a2:a10");B2))

Boisgontier
Bonjour Boisgontier,

Je te joins un fichier avec un complément d'explication car si ta solution fonctionne, elle ne répond pas à ma problématique... me semble t-il!!!

Merci d'avance pour ton aide.
 

Pièces jointes

  • Somme3DCondEns.xlsm
    24.7 KB · Affichages: 3

Airone784

XLDnaute Occasionnel
Bonjour


Exemple en PJ:

Nom de champ
nf=STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")
nfx=DECALER(Recap!$G$2;;;NB.SI(Recap!$G$2:$G$18;"><"&""))

=SOMMEPROD(SOMME.SI.ENS(INDIRECT("'"&nfx&"'!b2:b10");INDIRECT("'"&nfx&"'!a2:a10");B2))

Boisgontier
NB : je ne connais pas le symbole ><, il veut dire la même chose que <> pour dire différent ????
 

BOISGONTIER

XLDnaute Barbatruc
Repose en paix
cf PJ


=SOMMEPROD(SOMME.SI.ENS(INDIRECT("'"&nfx&"'!b2:b10");INDIRECT("'"&nfx&"'!a2:a10");$J21;INDIRECT("'"&nfx&"'!c2:c10");K$20))

Boisgontier
 

Pièces jointes

  • Somme3DCondEnsCommencepar012cond.xlsm
    25.6 KB · Affichages: 10

Airone784

XLDnaute Occasionnel
Bonjour à tous,

Le dernier fichier est bien mais si je veux sur une ligne faire le total des feuilles qui commencent par 1 sur une ligne c'est bon. Mais si je veux en même temps sur une autre ligne faire un total pour les feuilles qui commencent par 2, la solution ne fonctionne pas et je ne vois pas comment gérer 2 listes de feuilles (une liste des feuilles en 01.... et une liste des feuilles en 02...) et adapter la formule en conséquence.
 

job75

XLDnaute Barbatruc
Bonjour Airone784, JB, le forum,

Avec cette fonction VBA c'est bien simple :
VB:
Function SommeFeuille(Nom$, CritereFeuille$)
Application.Volatile
Dim w As Worksheet
CritereFeuille = LCase(CritereFeuille) 'pour ignorer la casse
For Each w In Worksheets
    If InStr(LCase(w.Name), CritereFeuille) Then _
        SommeFeuille = SommeFeuille + Application.SumIf(w.Columns(1), Nom, w.Columns(2))
Next
End Function
A placer impérativement dans un module standard pour qu'on puisse l'utiliser dans une feuille de calcul, voyez le fichier joint.

A+
 

Pièces jointes

  • Somme3DCondEns(1).xlsm
    35.2 KB · Affichages: 8

Airone784

XLDnaute Occasionnel
Bonjour Job75,

Je te remercie pour ta réponse qui est vraiment bien.
En revanche, je dois t'avouer que j'aimerais ne pas user du VBA sur mon fichier pour la simple est bonne raison qu'en cas de dysfonctionnement, le VBA n'est que peu utilisé et maîtrisé dans mon cadre professionnel et que cela impose à ce que je maintienne le fichier tout seul ce qui ne sécurise pas notre organisation. A l'inverse, des bonnes vieilles formules excel sont plus à la portée de différents utilisateurs notamment au moment de corriger des anomalies.
C'est d'ailleurs la raison pour laquelle j'ai fait appel au forum sur le terrain de la formule car en VBA, j'ai quelques compétences qui m'auraient surement permis de répondre à mon besoin...

Encore une fois merci et dans l'attente d'éventuelles autres réponses superbement FORMULEES.
 

job75

XLDnaute Barbatruc
Re, salut eriiiic,

Je ne vois vraiment pas quelle différence l'utilisateur ignorant peut faire entre une formule Excel utilisant une fonction macro Excel 4.0 et une formule (bien plus simple) utilisant une fonction personnalisée VBA...

Dans les 2 cas de toute façon il faudra que les macros soient activées.

Autre solution VBA, plus complète, avec des macros évènementielles dans la feuille "Recap" :
VB:
Private Sub Worksheet_Activate()
Dim CritereFeuille, d As Object, w As Worksheet, tablo, i&, x$
CritereFeuille = LCase([C1]) 'pour ignorer la casse, à adapter
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
For Each w In Worksheets
    If InStr(LCase(w.Name), CritereFeuille) Then
        tablo = w.UsedRange.Resize(, 2) 'matrice, plus rapide
        For i = 2 To UBound(tablo)
            x = tablo(i, 1)
            If x <> "" Then d(x) = d(x) + tablo(i, 2)
        Next
    End If
Next
'---restitution---
Application.EnableEvents = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [B3] 'à adapter
    If d.Count Then
        .Resize(d.Count) = Application.Transpose(d.keys) 'Transpose est limitée à 65536 lignes
        .Cells(1, 2).Resize(d.Count) = Application.Transpose(d.items)
        .Resize(d.Count, 2).Borders.Weight = xlHairline
    End If
    .Offset(d.Count).Resize(Rows.Count - d.Count - .Row + 1, 2).Delete xlUp 'RAZ dessous
End With
With UsedRange: End With 'actualise la barre de défilement verticale
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheet_Activate 'lance la macro
End Sub
Fichier (2).

A+
 

Pièces jointes

  • Somme3DCondEns(2).xlsm
    43.6 KB · Affichages: 6

Discussions similaires