Fonction recherchev et nom d'onglet automatique
Bonjour
Je vous remercie d'avance pour les réponse à mon problème.
Donc j'ai deux fichier xls
Fichier 1 et Fichier 2
Dans le fichier 1 j'ai des onglets avec des années 2018, 2019, 2020
Dans le fichier 2 j'ai aussi des onglets des années 2018,2019,2020
Dans le fichier 2 dans l'onglet 2018, j'ai cet formule dans une cellule
=SI(ESTVIDE(I7);"";RECHERCHEV(I7;'[Fichier1.xlsx]2018'!$A$14:$AJ$706;8;FAUX))Ce qui me donner la valeur de la cellule du fichier 1 suivant la valeur de la cellule du fichier 2
Donc je voudrais que la valeur de l'onglet ne soit inscrit en dur mais suivant la valeur du l'onglet où se trouve la cellule
J'ai dans une cellule du l'onglet cette formule qui m'affiche le nom de la cellule
=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;32)Donc comment faire pour que je puise utiliser
'[Fichier1.xlsx] valeur de la cellule BE10'!$A$14:$AJ$706
ou
'[Fichier1.xlsx] STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;32)'!$A$14:$AJ$706
Exemple si je suis dans l'onglet 2018, c'est 2018 qui est affiche par contre si la formule se trouve dans l'onglet 2019, c'est 2019 qui est affiche.
Je voudrais faire cela automatiquement parce que cette formule est inscrit dans plus de 600 cellule dans chaque onglet et donc pour ne pas rentrer à chaque fois la valeur de la cellule je voudrais faire cela automatiquement
Merci d'avance
PS : le fichier 1 est par fois fermer et parfois ouvert quand le fichier 2 est ouvert
Bonjour nunizgb,
Je pense que la fonction que tu cherches est INDIRECT qui te permet de passer des variables en paramètres de fonction
Exemple:
=SOMME(A1:B2) ==> Si tu tapes ça dans ta barre de formule la fonction va bien fonctionner
=SOMME("A1" & ":" & "B2") ==> ça écrira bien A1:B2 mais comme c'est du texte, la fonction ne va pas considérer A1 et B2 comme les paramètre de la fonction SOMME
En revanche si tu tapes
=SOMME(INDIRECT("A1" & ":" & "B2")) ==> Tu va bien écrire en texte A1:B2 mais INDIRECT va forcer Excel à les considérer comme des paramètres de fonction et donc fonctionner correctement
Du coup ce que tu cherches à faire:
'[Fichier1.xlsx] valeur de la cellule BE10'!$A$14:$AJ$706
Ressemblera à ça
=INDIRECT("[Fichier1.xlsx]" & BE10 & "!$A$14:$AJ$706") ==> En BE10 tu mets l'année souhaitée
Merci PinkRabbit pour la réponse, je viens de faire un essaie avec le fichier1 fermer et donc quand je met dans la formule la function indirect comme ceci :
=SI(ESTVIDE(I7);"";RECHERCHEV(I7;INDIRECT("[Fichier1.xlsx]" & BE10 &"!$A$14:$AJ$706");8;FAUX))J’obtiens l’erreur #REF !
Sachant que dans la cellule BE10 j'ai cette formule :
=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;32)Qui m'affiche bien le nom du l'ongle soit 2018, par contre si je fait un test avec ESTNUM(BE10), j'obtiens le résultat FAUX.
Donc j'ai essaye de mettre dans la cellule BE10 cette formule afin de convertir texte en nombre :
CNUM(STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;32))mais j'ai toujours dans ma formule recherchev comme résultat #REF!, donc comment je puisse trouver pourquoi cela m'affiche erreur #REF!.
J'ai aussi cette erreur quand le fichier1 est ouvert
Merci pour l'aide et la réponse
Bonjour nunizgb,
si tu as #REF! C'est que le nom est mal saisi
Je te recommande d'essayer dans une cellule à part de mettre ="[Fichier1.xlsx]" & BE10 &"!$A$14:$AJ$706" et de comparer à ce que tu obtiens en texte par rapport à ce que tu devrais obtenir
C'est normal que BE10 puisque tu utilises STXT pour retourner ta valeur
=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;32)
D'ailleurs pas besoin que ce soit un nombre puisque la référence de ton fichier est une chaine de caractère
Essaie ça
"'[Fichier1.xlsx]" & BE10 &"'!$A$14:$AJ$706" dans ton indirect (J'ai rajouté des ' )
Bonjour PinkRabbit
Merci pour l'aide donc quand j'ai mis dans une cellule à part ="[Fichier1.xlsx]" & BE10 &"!$A$14:$AJ$706" comme résultat j'obtiens ==> [Fichier1.xlsx]2018!$A$14:$AJ$706
Donc le nom du l'onglet est bien affiche
J'ai bien mis dans mon indirect "'[Fichier1.xlsx]" & BE10 &"'!$A$14:$AJ$706" avec des ' mais j'ai toujours cette erreur #REF par contre si le fichier1 est ouvert pas d'erreur donc la formule marche mais comment je puisse faire pour que cela marche si le Fichier1 est ferme ?
J'ai lu sur l'internet qu'il existe une fonction INDIRECT.EXT mais je n'est pas vu son code vba afin que je puisse utiliser cette fonction au lieu de INDIRECT.
Ou faire dans vba que le fichier1 soit accessible dès qu'on ouvre Fichier2 mais que le fichier1 ne soit pas accessible donc ouvert. Puis dès qu'on ferme Fichier1 la connexion avec Fichier1 soit aussi fermer.
Bonjour à tous,
Un essai ... =SI(ESTVIDE(I7);"";RECHERCHEV(I7;INDIRECT("[Fichier1.xlsx]" & BE10 &"!$A$14:$AJ$706");8;FAUX))
Si le fichier1.xlsx et ouvert, ça fonctionne.
ric
Salut nunizgb,
J'avais pas vu que ça marchait quand le fichier était ouvert, ce n'est donc pas ta formule qui déconne.
Quand ton fichier est ouvert, Excel fait référence au classeur comme ceci [Fichier1.xlsx]
Par contre quand le fichier est fermé, la référence se fait selon le chemin d'accès au fichier
'C:\Users\xxxx\Bureau\yyyy\[Fichier1.xlsx]2018'!$G$6
Donc dans ta formule INDIRECT rajoute juste le chemin d'accès (C:\Users\xxxx\Bureau\yyyy\ ceci dans mon exemple). Cependant cela fonctionne tant qu'on ne te change pas le fichier de place, le nom du fichier ou n'importe quel nom de dossier du chemin d'accès
Bonjour Ric et PinkRabbit,
Merci pour votre aide
PinkRabbit j'ai mis dans mon indirect le chemin d’accès donc ma formule est :
=SI(ESTVIDE(I7);"";RECHERCHEV(I7;INDIRECT("'D:\XXX\YYY\ZZZ\[Fichier1.xlsx]" & BE10 &"'!$A$14:$AJ$706");8;FAUX))Mais toujours erreur #REF
Par contre si je met cette formule avec onglet en dur cela marche avec fichier fermer
=SI(ESTVIDE(B6);"";RECHERCHEV(I7;'D:\XXX\YYY\ZZZ\[Fichier1.xlsm]2018'!$$A$14:$AJ$706;2;FAUX)))Si je fait cette formule :
=SI(ESTVIDE(B6);"";RECHERCHEV(I7;'D:\XXX\YYY\ZZZ\[Fichier1.xlsm]" & BE10 &"'!$D$8:$G$17";2;FAUX)))J'obtiens comme l'erreur #VALEUR
Donc comment remplacer ce 2018 par la valeur de la cellule BE10 ?
Merci pour votre aide
Salut nunizgb,
J'ai fait des tests, sans succès et je suis allé sur la page Microsoft de la fonction indirect:
"
La fonction INDIRECT renvoie uniquement le résultat d’une référence à un fichier ouvert. Si un classeur dont la fonction indirecte fait référence indirectement est fermé, la fonction renvoie une #REF ! «.
"
Je creuse et je reviens vers toi !
Edit:
Est-ce qu'une macro VBA fonctionnerait pour toi ?
Merci PinkRabbit
Un code vba oui pourra aussi faire affaire parce que mon fichier comporte déjà moi aussi de mon coté je regarde comment je puisse faire mais comme je ne susi pas bon en vba je ne sais pas comme faire et de plus avec un code que je test pour un autre problème j’ai une erreur 40040.
en tous cas merci pour ton aide
nunizgb,
Peux-tu me partager tes fichiers "officiels" que je fasse les macros ?
Pour le fonctionnement je peux te proposer :
* Un bouton qui va actualiser les données de la page en fonction de la date rentrée dans la cellule de référence
* Actualisation des données seulement quand on change la date dans la cellule de référence
Dis moi ce qui te convient le plus