Aide pour formules INDIRECT

Bonjour à tous.

Je ne suis pas experte en Excel et je sollicite à nouveau votre aide pour le petit fichier ci-dessous :

Les formules que j'ai introduites dans le fichier (Test3) ne fonctionnent pas.

J'ai besoin :

- en colonne A, d'aller rechercher les dates dans la colonne A du fichier (Test4) en fonction de l'année saisie en C9

La formule introduite est la suivante : =INDIRECT(ADRESSE(PETITE.VALEUR(SI(ANNEE(INDIRECT("'"&$B$7&"'!$A$2:$A$20000"))=$C$9;LIGNE(INDIRECT("'"&$B$7&"'!$A$2:$A$20000")));LIGNE()-LIGNE($A$12)+1);2;1;1;$B$7))

- en colonne B, d'aller rechercher les valeurs dans la colonne E du fichier (Test4)

La formule introduite est la suivante : =SIERREUR(INDEX(INDIRECT("'"&$B$7&"'!$i$2:$k$20");PETITE.VALEUR(SI(INDIRECT("'"&$B$7&"'!$b$2:$b$20")=A12;LIGNE($A$2:$A$19)-1;"");NB.SI($A$12:$A12;$A12));1);"")

- en colonne C, d'aller rechercher les valeurs dans la colonne H du fichier (Test4)

La formule introduite est la suivante =SIERREUR(INDEX(INDIRECT("'"&$B$7&"'!$i$2:$k$20");PETITE.VALEUR(SI(INDIRECT("'"&$B$7&"'!$b$2:$b$20")=$A12;LIGNE($A$2:$A$19)-1;"");NB.SI($A$12:$A12;$A12));3);"")

Voici le fichier où il faut aller chercher les valeurs :

Je vous remercie pour votre aide

Bonjour,

à savoir : INDIRECT ne fonctionne pas avec les fichiers fermés, je crois.

@ bientôt

LouReeD

Bonjour

Il faudrait intégrer le nom du classeur dans INDIRECT mais cela ne marcherait que le le classeur est ouvert...

As-tu le moyen d'utiliser l'add on PowerQuery, intégré à partir de 2016 mais à télécharger et installer sur 2013...

Attention : il faudra que ton fichier Test4 soit ouvert pour que INDIRECT fonctionne (il ne fonctionne pas sur fichier fermé, il y a un moyen de le contourner par macro)

Je n'ai pas tout regardé mais déjà ta cellule $B$7 ne contient même pas le nom du fichier Test4, alors qu'un lien simple s'écrit :

=[Test4.xlsx]ISP_Lipase!$A$2

ps : bonjour LouReeD et Chris qui m'on doublé

78chris a écrit :

Bonjour

Il faudrait intégrer le nom du classeur dans INDIRECT mais cela ne marcherait que le le classeur est ouvert...

As-tu le moyen d'utiliser l'add on PowerQuery, intégré à partir de 2016 mais à télécharger et installer sur 2013...

Merci 78chris pour la réponse.

Malheureusement ce sont des fichiers qui sont utilisés su mon lieu de travail et la charte informatique nous interdit de télécharger d'autres logiciels que ceux installés sur les PC...

Pourrais-tu, STP, me dire où je dois insérer le nom du classeur pour que ma formule fonctionne ?

Dois-je introduire le nom du classeur ou le nom de l'onglet dans le classeur ?

Merci ++

Il faudrait qu'en B7 il soit écrit [Test4.xlsx]ISP_Lipase

Steelson a écrit :

Il faudrait qu'en B7 il soit écrit [Test4.xlsx]ISP_Lipase

Je t'avoue que je suis un peu perdue...

Je l'ai fait mais ça ne fonctionne toujours pas même si j'ouvre le fichier Test3 :

LouReeD a écrit :

Bonjour,

à savoir : INDIRECT ne fonctionne pas avec les fichiers fermés, je crois.

@ bientôt

LouReeD

Merci LouReeD

Bichounette2006 a écrit :

Je l'ai fait mais ça ne fonctionne toujours pas même si j'ouvre le fichier Test3 :

Test4.xlsx

Maintenant il faut voir la formule elle-même ... est-ce qu'elle fonctionne en dehors de la variable introduite par INDIRECT. Il faut d'abord faire la mise au point de la formule et passer ensuite à la mise en variable du fichier source. Or je n'ai pas bien compris le sens de ta formule !

En fait ta plage est bien trop grande et couvre des zones texte et pas seulement des dates.

En plus tu peux t'appuyer sur la structure tableau

Ta formule de base sans INDIRECT qui fonctionne est :

=INDIRECT(ADRESSE(PETITE.VALEUR(SI(ANNEE(Test4.xlsx!Tableau1[Date])=$C$9;LIGNE(Test4.xlsx!Tableau1[Date]));LIGNE()-LIGNE($A$12)+1);2;1;1;$B$7))

Et avec INDIRECT ... dans le fichier Test3, avec en B7 seulement le nom du fichier puisqu'on utilise le nom du tableau dans la formule

=INDIRECT(ADRESSE(PETITE.VALEUR(SI(ANNEE(INDIRECT($B$7&"!Tableau1[Date]"))=$C$9;LIGNE(INDIRECT($B$7&"!Tableau1[Date]")));LIGNE()-LIGNE($A$12)+1);2;1;1;$B$7))

Steelson a écrit :

En fait ta plage est bien trop grande et couvre des zones texte et pas seulement des dates.

En plus tu peux t'appuyer sur la structure tableau

Ta formule de base sans INDIRECT qui fonctionne est :

=INDIRECT(ADRESSE(PETITE.VALEUR(SI(ANNEE(Test4.xlsx!Tableau1[Date])=$C$9;LIGNE(Test4.xlsx!Tableau1[Date]));LIGNE()-LIGNE($A$12)+1);2;1;1;$B$7))

Et avec INDIRECT ... dans le fichier Test3, avec en B7 seulement le nom du fichier puisqu'on utilise le nom du tableau dans la formule

=INDIRECT(ADRESSE(PETITE.VALEUR(SI(ANNEE(INDIRECT($B$7&"!Tableau1[Date]"))=$C$9;LIGNE(INDIRECT($B$7&"!Tableau1[Date]")));LIGNE()-LIGNE($A$12)+1);2;1;1;$B$7))

Merci Steelson.

En effet, une fois que le fichier Test4 est ouvert, les formules que tu as modifiées vont bien rechercher des valeurs mais malheureusement pas les bonnes (j'ai certainement merdé qch ).

En fait, il faut aller rechercher :

• les valeurs de la colonne E pour insérer dans la colonne B
• les valeurs de la colonne H pour insérer dans la colonne C

Re

Bichounette2006 a écrit :

Merci 78chris pour la réponse.

Malheureusement ce sont des fichiers qui sont utilisés su mon lieu de travail et la charte informatique nous interdit de télécharger d'autres logiciels que ceux installés sur les PC...

Ce n'est pas vraiment un autre logiciel mais un composant Microsoft : une demande au service informatique peut peut-être aboutir...

Des formules matricielles avec INDIRECT et l'obligation d'ouvrir le fichier Test4 est loin d'être optimisé.

Une requête MsQuery (intégré) est possible avec le choix de l'année mais je ne pense pas qu'on puisse mettre en paramètre le fichier et l'onglet... Il faudrait également supprimer les lignes au dessus du tableau pour pouvoir utiliser MsQuery

Bichounette2006 a écrit :

En effet, une fois que le fichier Test4 est ouvert, les formules que tu as modifiées vont bien rechercher des valeurs mais malheureusement pas les bonnes (j'ai certainement merdé qch ).

En fait, il faut aller rechercher :

• les valeurs de la colonne E pour insérer dans la colonne B
• les valeurs de la colonne H pour insérer dans la colonne C

Je te remercie pour ton aide

Voici

Si tu veux maintenant pouvoir faire ce lien fichier fermé, alors il faudra passer à une macro (assez simple du reste, mais c'est autre chose)

78chris a écrit :

Re

Bichounette2006 a écrit :

Merci 78chris pour la réponse.

Malheureusement ce sont des fichiers qui sont utilisés su mon lieu de travail et la charte informatique nous interdit de télécharger d'autres logiciels que ceux installés sur les PC...

Ce n'est pas vraiment un autre logiciel mais un composant Microsoft : une demande au service informatique peut peut-être aboutir...

Des formules matricielles avec INDIRECT et l'obligation d'ouvrir le fichier Test4 est loin d'être optimisé.

Une requête MsQuery (intégré) est possible avec le choix de l'année mais je ne pense pas qu'on puisse mettre en paramètre le fichier et l'onglet... Il faudrait également supprimer les lignes au dessus du tableau pour pouvoir utiliser MsQuery

Merci pour tes explications... je vais voir ce que je peux faire !

Steelson a écrit :

Voici

Si tu veux maintenant pouvoir faire ce lien fichier fermé, alors il faudra passer à une macro (assez simple du reste, mais c'est autre chose)

Merci Steelson.

C’est parfait !

Pourrais-tu m’expliquer comment faire une macro. Je n’ai jamais fait ça mais je suis prête à apprendre 🤔

Bon, ce n'est pas un cas si simple que cela bien que la macro soit vraiment brève.

Il s'agit de créer une macro événementielle qui va se déclencher en cas de changement de la cellule B7 :

https://www.excel-pratique.com/fr/vba/evenements_feuille.php

et notamment :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B7")) Is Nothing Then Exit Sub
    MsgBox "Nouvelle valeur : " & Range("B7").Value
End Sub

Maintenant qu'on a capté le changement de valeur de B7 et son nouveau contenu, on va reconstruire par VBA la formule de base (sans indirect) fichier fermé en appliquant FormuleR1C1 ou FormuleLocal

Mais il faut aussi changer ta formule pour ne pas utiliser INDIRECT(ADRESSE

Je vais regarder pour trouver une formule équivalente.

Steelson a écrit :

Bon, ce n'est pas un cas si simple que cela bien que la macro soit vraiment brève.

Il s'agit de créer une macro événementielle qui va se déclencher en cas de changement de la cellule B7 :

https://www.excel-pratique.com/fr/vba/evenements_feuille.php

et notamment :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B7")) Is Nothing Then Exit Sub
    MsgBox "Nouvelle valeur : " & Range("B7").Value
End Sub

Maintenant qu'on a capté le changement de valeur de B7 et son nouveau contenu, on va reconstruire par VBA la formule de base (sans indirect) fichier fermé en appliquant FormuleR1C1 ou FormuleLocal

Mais il faut aussi changer ta formule pour ne pas utiliser INDIRECT(ADRESSE

Je vais regarder pour trouver une formule équivalente.

Oulala... Steelson... je suis complètement perdue... c’est presque du chinois pour moi sur ce coup là...

Je vais jeter un œil sur le lien que tu m’as envoyé.

Un tout grand merci pour ton aide et désolée d’etre larguée

Bon, ben de toute façon, comme il te faut une formule matricielle et que celle-ci est incompatible aussi fichier fermé, je ne peux pas aller plus loin.

Il faudra donc bien que ton fichier soit ouvert !

Bonjour,

pas trop le temps de me pencher là dessus, mais si on peut récupérer le tableau "source" du fichier "fermé", pour le mettre en tableau VBA, puis effectuer la formule matricielle sur ce tableau avec par exemple EVALUATE, peut être alors Est-ce possible, puis d'écrire "en dur" dans le fichier ouvert "résultat" le résultat des différents calculs...

@ bientôt

LouReeD