XL 2016 Utiliser un équivalent de INDIRECT sur des classeurs fermés

[Deuch230]

Bonjour à tous,

Je suis inscrit tout récemment sur ce site qui est une mine d'or et j'ai déjà trouvé beaucoup de réponses à mes problèmes sans avoir à à ouvrir un nouveau fil mais là je sèche lamentablement...

Voici mon souci:
Dans le cadre du suivi des notes d'une promotion d'élèves pilotes, j'ai créé les fichiers suivants:
- un fichier par élève (~nom_élève.xlsm) qui contient une fiche de notation par mission (il y en a 120 et elles ont toute un nom différent) ainsi qu'onglet de synthèse des notes (nommé SNAP),
- un fichier de statistiques (~stats.xlsm) qui récupère l'ensemble des notes de tous les élèves de la promotion (missions + d'autres choses).

Ce fichier statistiques doit récupérer dans chaque fichier de notation élève la note, la date, l'instructeur, le temps de vol pour chaque mission (dont le nom est variable).
J'ai donc tenté une formule avec INDIRECT("'[nom_élève.xlsm]"& cellule qui contient le nom de la mission dans le classeur stats & "'!cellule qui contient la valeur cherchée dans le fichier élève") genre =INDIRECT("'[Eleve.xlsx]"& C2 & "'!B2").

Mais cela ne marche pas vers un classeur fermé et je n'arrive pas à trouver de solution de contournement pour alimenter simplement ce fichier de stats avec mes pauvres connaissances: j'aimerais que la formule (ou le code) aille chercher la note de chaque mission dans l'onglet concerné et ce pour chaque élève, sans avoir à écrire une formule pour chaque mission et chaque élève (120 x 15 = trop !).
La formule ou le code doit donc aller chercher le nom de la mission dans le tableau de stats puis la note dans l'onglet SNAP de chaque fichier élève.

Les fichiers sont sur un réseau sécurisé, je ne peux pas les exporter mais voici un exemple...
La version Excel au boulot est 2016 (malheusement...).

Merci d'avance pour votre aide précieuse !

 

[chris]

Bonjour
La structure de ton classeur semble très ancienne et peu optimisée pour des stats simples et efficaces

On peux néanmoins utiliser tous les onglets autre que SNAP de tous les classeurs d'un dossiers pour synthétiser par PowerQuery (intégré à Excel) par élève et pour l'ensemble.

Reste à préciser quel code (nom onglet) correspond à un vol ou un simulateur : on 'a pas d'infos ligne 17 de SNAP

Je ne pourrais m'en occuper avant ce soir

 

[Deuch230]

Bonjour Chris,
C'est effectivement de l'ancien... J'ai repris le secteur instruction il y a peu et je dois me débrouiller avec les fichiers existants pour les promos en cours le temps de refaire l'architecture...

Les fichiers joints ne sont qu'un exemple vite fait, car je ne peux pas exporter les fichiers réels qui sont confidentiels, pour montrer qu'il y a du vol et du simu sur une même colonne ce qui fait que je ne peux pas utiliser RECHERCHEV ou H et RECHERCHEX n'existe pas sur 2016...

 

[chris]

RE

La question est qu'est-ce que sont les lignes 17 et plus de SNAP : vol ou simulation et si tu n'y réponds pas on ne rien faire...

De même on ne sait d'où sortent les dates, les durées, les IP (Instucteurs ?)

Donner une solution sans tous les éléments du problème n'est pas faisable

On ne te demande pas de fichiers réels mais des exemples représentatifs

 

[Deuch230]

Merci pour ton temps Chris.
Ces données viennent des feuilles missions qui sont beaucoup plus complexes que dans l’exemple mais honnêtement ça n’a aucune importance vis à vis de mon problème, il n’y a donc pas lieu de s’en inquiéter.
Peu importe les simulateurs et les vols également, le tableau snap en exemple illustre le fait que je ne peux pas utiliser RECHERCHE V ni RECHERCHE H car les colonnes et les lignes sont empilées.
La formule INDIRECT dans SNAP que j’utilise sous GH1 et autres fonctionne parfaitement mais uniquement avec des classeurs ouverts, or je ne peux pas demander à mon client d’ouvrir tous les fichiers de tous les élèves pour mettre à jour leurs statistiques…
Je n’arrive pas à trouver le moyen de remplacer cette formule, mais avec un coup de pouce je pourrais décliner selon mes contraintes.

 

[job75]

Bonsoir Deuch230, chris,

Téléchargez les fichiers joints dans le même dossier et exécutez cette macro :

Sub MAJ()
Dim chemin$, fichier$, ncol%, fich$, c As Range, col%, cc As Range
chemin = ThisWorkbook.Path & "\"
fichier = Dir(chemin & "*.xlsx") '1er fichier du dossier
ncol = 16 'nombre de colonnes du tableau de Stats, à adapter
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
While fichier <> ""
    fich = Left(fichier, Len(fichier) - 5)
    Set c = Columns(1).Find(fich, , xlValues, xlWhole, , xlPrevious)
    If Not c Is Nothing Then
        With Workbooks.Open(chemin & fichier).Sheets("SNAP") 'ouverture du fichier
            For col = 3 To ncol
                Set cc = .Cells.Find(c(1, col))
                If Not cc Is Nothing Then
                    c(2, col) = cc(3)
                    c(3, col) = cc(4)
                    c(4, col) = cc(2)
                    c(5, col) = cc(5)
                End If
            Next col
            .Parent.Close False
        End With
    End If
    fichier = Dir 'fichier suivant
Wend
Application.Calculation = xlCalculationAutomatic
End Sub

A+

 

[Deuch230]

Merci beaucoup Job75, j’essaie cela dès que possible

 

[Deuch230]

Bonjour Job75,
Désolé pour le délai de réponse, j’ai mis du temps à refaire les adresses et à ajuster les liens…
C’est très efficace, merci beaucoup !

 

[job75]

Bonjour Deuch230,

Vous avez dû voir qu'il fallait mettre les lignes des dates au format Date et les lignes des durées au format [h]:mm.

A+