Microsoft 365 Cellule intermédiaire de référence à une autre page dans une fonction

[Gloubit]

Bonjour à tous,

Je souhaite créer sur la feuille 1 de mon classeur excel une liste nominative de personnes me permettant d'un seul coup d'oeil de savoir qui dispose d'une certaine compétence. Comme la page 1 comporte d'autre infos que je ne veux pas noyer avec un tableau listant toutes les compétences référencées qui sont nombreuses, je souhaiterais qu'en saisissant dans une cellule "recherche" l'une d'elles, une flèche apparaisse dans la cellule à gauche du nom de la personne si elle l'a. Sur les feuilles suivantes se trouvent les fiches individuelles des personnes (une feuille = une personne), avec le statut pour chacune de ces compétence (Oui/Non).
Pour le premier Nom de la liste j'ai appliqué la formule suivante:

=SI(OU(ET(B2="compétence_1";feuille2_nom1!D4="Oui");ET(B2="compétence_2";feuille2_nom1!D5="Oui"));"=>";"") en répétant la formule ET( autant de fois qu'il y a de compétences.

Ca marche bien, c'est peut-être un peu lourd et pas très élégant, mais pour moi qui apprends sur le tas ça me convient pour le résultat... à un détail près.

Je voudrais pouvoir réutiliser la fonction/fichier pour les autres noms et d'autres listes, or les feuilles étant nominatives, il faudrait reprendre toutes les références de page à chaque fois...

Mon idée était de créer sur la feuille 1 une cellule de référence à la page à atteindre. Mais je ne sais pas quelle imbrication de fonction utiliser, ni même s'il en existe une sans avoir à passer par une Macro. J'ai tenté cela et plusieurs syntaxe du genre mais sans résultat:

ET(B2="compétence_1";INDIRECT(C4;"!D4="Oui""));ET(...

feuille 1:

Filtre	Compétence_1
=>	Nom 1		Feuille2_nom1
	Nom 2		Feuille3_nom2

Merci pour vos retours et suggestions, bien à vous.

 

[sylvanu]

Bonjour Gloubit et bienvenu sur XLD,
Un petit fichier test représentatif et anonyme permettrait de mieux cerner votre problème et d'essayer d'apporter une solution.
Un petit fichier, comme un petit dessin, vaut mieux qu'un long discours.

 

[Gloubit]

Bonjour sylvanu, merci pour votre retour.

Voici le fichier test, j'espère que ça illustrera comme il faut ma question.

 

[sylvanu]

Re,
En PJ un essai avec cette macro :

Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [B2:ZZ2]) Is Nothing Then
        Competence = Target: Ligne = 3
        Range(Cells(3, Target.Column), Cells(65000, Target.Column)).ClearContents
        For Each F In Worksheets
            If F.Name <> ActiveSheet.Name Then
                If Application.CountIf(F.Range("A1:ZZ10000"), Competence) > 0 Then
                For Col = 1 To 700
                    L = 0: On Error Resume Next
                    L = Application.Match(Competence, F.Column(Col), 0)
                    If L <> 0 And F.Cells(L, Col + 1) = "Oui" Then
                        Cells(Ligne, Target.Column) = F.Name
                        Ligne = Ligne + 1: Exit For
                    End If
                Next Col
            End If
        End If
    Next F
    End If
End Sub

La mise à jour se fait automatiquement quand on modifie une valeur dans la ligne 2 de la feuille Synthèse.