récupérer le nom d'une feuille dans une cellule ?

[LMK]

Bonjour

Je recherche une fonction qui à priori n'existe pas, mais comme je n'en suis pas sur je préfère demander ici, apres tout on sait jamais

Voici donc "l'énigme"...

J'ai un classeur avec 11 feuilles, toutes nommées par un numéro de "00" à "10".
Dans la case B1 des feuilles "01" à "10" se trouve un nombre entier.
Dans la case B1 de la feuille "00" se trouve la plus grande valeur des cases B1 des feuilles "01" à "10", obtenue avec la fonction simple suivante =MAX('01:10'!B1)

Je voudrais faire apparaitre dans la case A1 de la feuille "00", le nom de la feuille dans laquelle se trouve la valeur obtenue en case B1.

Par exemple, si la valeur maxi des cases B1 se trouve dans la feuille "07", je voudrais que "07" s'affiche automatiquement dans la case A1 de la feuille "00".

Vous me direz qu'il est assez rapide de regarder la valeur de la case B1 de chaque feuille et de noter "manuellement" le nom de la feuille ou se trouve la valeur maxi.
Mais le souci c'est que j'ai besoin de cette fonction pour 2000 lignes, et la c'est beaucoup moins rigolo de chercher la feuille correspondante manuellement pour les 2000 valeurs maxi

Voila j'espère avoir assez bien décrit ce que je recherche lol, merci d'avance pour vos éventuelles réponses

Bonne année!

 

[wilfried_42]

Re : récupérer le nom d'une feuille dans une cellule ?

Bonjour et bienvenue

Il n'y a pas de formule pour cela, mais des macro Excel4.00 le font tres bien
Regarde ce fil, une simulation sans VBA d'une Recherche Multifeuille
Le fichier est en fin de fil

 

[LMK]

Re : récupérer le nom d'une feuille dans une cellule ?

merci wilfried42 je vais voir ça

 

[PMO2]

Re : récupérer le nom d'une feuille dans une cellule ?

Bonjour,

Une piste avec une fonction personnalisée.
Copiez le code ci-desssous dans un module standard.

Function pmo_nomFeuilleMaxi(Cellule As Range) As String
Dim i&
Dim Maxi
Dim Nom$
Application.Volatile
For i& = 2 To 11
  With Sheets(i&)
    If Maxi < .Range(Cellule.Address) Then
      Maxi = .Range(Cellule.Address)
      Nom$ = .Name
    End If
  End With
Next i&
pmo_nomFeuilleMaxi = Nom$
End Function

Pour l'utiliser, et en prenant votre exemple, tapez en "A1" de la feuille "00"
=pmo_nomFeuilleMaxi(B1)

Cela ne fonctionne que si les feuilles sont bien dans l'ordre de "00" à "10".

Cordialement.

PMO
Patrick Morange

 

[ROGER2327]

Re : récupérer le nom d'une feuille dans une cellule ?

Bonjour à tous.

Une réponse en VBA :

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Cells(1, 1).Address = "$A$1" Then
    Cancel = True
    Dim sh, num, r As String
        For Each sh In ThisWorkbook.Worksheets
            If sh.Name <> Me.Name Then
                With Worksheets(sh.Name).Cells(1, 2)
                    If Not IsEmpty(.Value) And IsNumeric(.Value) Then
                        If IsEmpty(num) Or .Value > num Then num = .Value: r = sh.Name
                    End If
                End With
            End If
        Next sh
        Me.Cells(1, 1) = r
    End If
End Sub

Ce code, placé dans le module de votre feuille 00, devrait faire l'affaire. Il fonctionne quel que soit le nom des feuilles et renvoie le nom de la première feuille contenant la plus grande valeur numérique dans sa cellule B1. (La feuille 00 est ignorée.)
Pour exécuter le code, faire un double-clic sur la cellule A1 de la feuille 00.​

ROGER2327

 

[LMK]

Re : récupérer le nom d'une feuille dans une cellule ?

Merci à tous pour vos réponses!

La solution de PMO2 fonctionne donc je ne suis pas allé plus loin, mais je garde l'astuce de Roger en note

Bonne nuit!

 

[wilfried_42]

Re : récupérer le nom d'une feuille dans une cellule ?

Bonjour à tous

Si tu es amené à faire des formules en multifeuille, je te suggère cette fonction personnalisée qui te permettra de créer tes formules sans ajouter de code VBA supplémentaire

Cette fonction permet de transformer des plages multifeuille en matrice et de d'utiliser ces matrices dans des formules.

Function M_Charge(plage As Range, Optional feuilles As String = "") As Variant
    Dim cel As Range, i As Long, j As Integer, tablo() As Variant
    Dim feuille1 As String, feuille2 As String
    Application.Volatile ' Permet un recalcul automatique
    i = -1
' Définition de la feuille par défaut si aucune feuille n'est mentionnée
    If feuilles = "" Then feuilles = ActiveSheet.Name & ":" & ActiveSheet.Name
' Récupération de la feuille de début et la feuille de fin
    feuille1 = Left(feuilles, InStr(feuilles, ":") - 1)
    feuille2 = Right(feuilles, Len(feuilles) - InStr(feuilles, ":"))
' Passage en revue de toutes les feuilles entre Feuille1 et Feuille2
    For j = Sheets(feuille1).Index To Sheets(feuille2).Index
        For Each cel In plage ' Pour chacune des cellules de la plage
            i = i + 1
            ReDim Preserve tablo(i) ' J'incrémente la table en cours de création
            tablo(i) = Sheets(j).Cells(cel.Row, cel.Column).Value ' Récupélation de la valeur
        Next
    Next j
    M_charge = tablo ' Affectation du tableau à la fonction (la matrice est créée)
End Function

dans ton cas, la formule sera une formule simple puisque Max fonction sur des plage en 3D

="Feuille : " & TEXTE(equiv(max('00:10'!B1);M_Charge(B1;"00:10"))-1;"00")