[Résolu]Nom feuille et fonction INDIRECT dans Calc et Excel

[luky-luke]

Bonjour,
Après une longue absence sur le forum, je reviens en coup de vent, peut-être pas à l'abri de reprendre goût à l'entraide et au macro, la retraite arrive . Bref j'ai besoins d'un petit coup de main pour tenter de résoudre un problème auquel j'ai un peu de mal à trouver une solution.
On me communique un tableur. Je ne peux pas toucher à la structure. Les noms des feuilles sont sous la forme
Feuille1_-_Du 16 octobre au 22 octobre et sont susceptible de changer de nom et/ou de date.
Je vous laisse imaginer le "chantier" dans les formules.
Ce fichier est utilisé sur plusieurs postes, certain avec LibreO et d'autre avec Office365 (C'est comme ça, j'ai arrêté de lutter ).
Pour essayer de simplifier l'affaire, je créer une feuille "BDD" dans la laquelle je regroupe par formule le nom des feuilles. Elle va me servir de référence pour "simplifier" et "étirer" les formules dans la feuille "Recap_Budget", et permettre le changement du nom des feuilles sans conséquences en principe pour les formules. Sur cette feuille (Recap_Budget), dans les cellules de la ligne 2, je note les coordonées des cellules que je dois aller chercher dans les feuille1,feuille2,3 etc. Je ne peux pas toucher à la structure des tableaux
C'est dans cette feuille ou j'ai un soucis.
La formule

Code : Tout sélectionner

=SIERREUR(INDIRECT(INDIRECT(CONCATENER("$BDD.A";$A5))&"."&B$2);"")

fonctionne avec LibreOffice. Mais notre ami Bill à du mal à la digérer. À l'ouverture les utilisateurs de la suite payante et non libre obtienne ça :

Code : Tout sélectionner

=SIERREUR(com.microsoft.single.indirect(com.microsoft.single.indirect(CONCATENER("$BDD.A";$A10))&"."&B$2);"")

Ça marche moins bien forcément !
Dans mes recherches je suis tombé sur ce fil...Bref je trouve pas grand chose
Définir une plage indexée (différence XLS)
Si quelqu’un à déjà rencontrer ça comme problème, une petite idée du contournement possible de l'erreur ?
En pièce jointe une version très simplifié mais qui donne quand même une petite idée du fichier que l'on me communique.

P.S.: J'ai pas l'OS de Bill et sa suite bureautique sous la main, donc je suis obligé de faire des tests pas des intermédiaires.

[Jeff]

Bonjour,

Les noms des feuilles [...] sont susceptible de changer de nom et/ou de date.

Les formules devraient être actualisées automatiquement (ça, c'est la théorie, à voir en pratique)

Les (malheureux) utilisateurs de la suite payante ouvrent le fichier ods, ou bien un xlsx ?

A +

Ajout : À tout hasard, que donne : Code : Tout sélectionner =SIERREUR(INDIRECT(INDIRECT(CONCATENER("$BDD!A";$A5))&"!"&B$2);"") avec Office 365

[luky-luke]

Bonjour tout le monde, bonjour Jeff,

Les (malheureux) utilisateurs de la suite payante ouvrent le fichier ods, ou bien un xlsx ?

Je viens de faire un essai, fichier enregistré en .XLS et .XLSX. Ouverture avec Office, la formule n'est pas reconnue.
OUPS ! je viens de lire ta proposition de formule, en envoyant ma réponse. Je reviens...après un éssai
Je pense que je vais prendre le temps de faire un fichier anonymisé plus complet et vous le soumettre, histoire de voir si vous n'auriez pas une idée de structure plus "intelligente" à me soumettre.

Cordialement
Luke
P.S.:Comme le fichier va être beaucoup plus facile à utiliser avec les nouvelles formules, cela va me faire un argument de plus pour l'harmonisation et l'utilisation de notre suite bureautique préférée.

[Jeff]

Sans test exhaustif, je dirais qu'avec un classeur.ods, ouvert avec Excel, les formules du type :

Code : Tout sélectionner

=BDD.A5

sont correctement interprétée.

En revanche, dans les mêmes conditions, une formule du type :

Code : Tout sélectionner

=INDIRECT("BDD.A5")

risque d'être mal digérée.

De mémoire, tu devrais pouvoir tester la version de la suite employée avec un truc du genre =INFO("version"), qui devrait fonctionner avec les deux suites bureautiques, et, selon les retours, proposer le texte adéquat qui sera interprété par INDIRECT.

A +

[luky-luke]

Merci pour tes réponses.
J'utilise depuis 1/2 heure un poste "Bill" avec Office installé (J'ai une poussé d'urticaire mais ça va aller )
Après ouverture du fichier .XLSX, je constate que le problème viens aussi de l'extraction du nom des feuilles. la formule

Code : Tout sélectionner

=DROITE(CELLULE("filename";'Feuille1_-_De-2007-A-2012'.A1);NBCAR(CELLULE("filename";'Feuille1_-_De-2007-A-2012'.A1)) - CHERCHE("$"; CELLULE("filename";'Feuille1_-_De-2007-A-2012'.A1);1))

ne fonctionne pas. le signe $ dans la fonction CHERCHE ne peut pas fonctionner car il est absent dans Exel.
Je vais clôturer ce sujet et en ouvrir un autre (peut-être...) Ou démissionner, j'hésite

Merci encore pour l’effort.
Cordialement
Luke

[Jeff]

le signe $ dans la fonction CHERCHE ne peut pas fonctionner car il est absent dans Exel

Tu fait même de l'urticaire au nom du produit, c'est Excel

À ma connaissance, les signes $ ont la même interprétation sur les 2 suites

Que renvoie :

Code : Tout sélectionner

=CELLULE("filename")

D'une manière générale, pour ne pas s'embêter avec les séparateurs de nom de feuille, le plus simple est de construire le nom avec une fonction ADRESSE (le tableur utilisé va mettre son propre séparateur), puis interpréter le texte obtenu avec INDIRECT.

A +

[luky-luke]

On est hors sujet, la patrouille va nous rattraper
Sur le post "Bill" la formule renvoie

Code : Tout sélectionner

\AdresseDuRépertoire\[Reformule_BILL_INDIRECT_.xlsx]BDD

BDD c'est le nom de la feuille.
A+
Luke

[Jeff]

Pour savoir si c'est l'indirection qui est mal supportée, à tester en récap_budget.B5 :

Code : Tout sélectionner

=INDIRECT(ADRESSE(LIGNE(INDIRECT(B$2));COLONNE(INDIRECT(B$2));;;INDIRECT(ADRESSE($A5;1;;;"BDD"))))

À étirer vers la droite et le bas.

Ci-joint un fichier-test avec AOO (j'ai viré les fonction SIERREUR)

A +

[luky-luke]

Merci Jeff!
Bon là j'ai plus accès à un poste windows/eCxel et je suis en vacances (dans 10 minutes) pour une semaine. Je testerai tout ça à tête reposée.
J'ai quand même fait un tour cet après-midi sur l'aide d'Ecxel, et les formules, que ce soit pour INDIRECT ou pour le renvoie de nom de feuille sont bougrement ressemblante quand même. Y'a un loup !
Je ne manquerai pas de donner des nouvelles.

Cordialement
Luke

[Jeff]

J'ai une version Excel sur smartphone qui ouvre en lecture seule, et j'ai toujours l'erreur sur la feuille BDD pour les noms de feuilles, je vais voir comment contourner ça si j'ai le temps...

A +
.

Ajout : Dans ce fichier joint : un nom de feuille saisi "en dur" dans la feuille BDD j'ai un calcul qui semble ok sur AOO et Excel (version de mon smartphone) une fonction INFO pour savoir si on est avec Calc, ou avec Excel toujours sur mon smartphone, Excel traduit bien la fonction (qui devient INFORMATIONS) mais affiche #VALEUR pour la formule : Code : Tout sélectionner =INFO("version") Ça va être bloquant pour calculer le nom de feuille À tester quand même, c'est peut-être lié à cette version d'Excel pour mobile. Reformule_BILL_INDIRECT_Jeff.ods  Ajout : En même temps, je teste l'ajout dans l'ajout... C'est ok pour la fonction INFO avec une version standard d'Excel (le texte 16.0 s'affiche) Dès lors, je pense que tu n'as guère de choix : - utiliser la fonction INFO en feuille BDD, pour choisir comment extraire le nom de feuille, un truc du genre : Code : Tout sélectionner =SI(INFO("version")="16.0";extraction Excel;extraction LibO) Je te laisse adapter extraction Excel et extraction LibO - utiliser une fonction ADRESSE en dernière feuille, qui permettra de retourner le séparateur de nom de feuille selon le tableur utilisé. .

[luky-luke]

Bonjour,
Merci Jeff pour la machine à gaz
Ce soir, en regardant un film "passionnant" , l'esprit vagabonde et... SUBSTITUTE
On est d'accord, le problème vient de la fonction CHERCHE dans la formule

Code : Tout sélectionner

DROITE(CELLULE("nomfichier";$Feuil2.A1);NBCAR(CELLULE("nomfichier";$Feuil2.A1))-CHERCHE("$";CELLULE("nomfichier";$Feuil2.A1);1))

Sous LibO le nom de la feuille est précédé du signe $ et chez Bill, c'est ]
Quitte à faire une formule à rallonge, je testerais bien un truc comme ça tout simplement.

Code : Tout sélectionner

=SIERREUR(DROITE(CELLULE("nomfichier";$BDD.A2);NBCAR(CELLULE("nomfichier";$BDD.A2))-CHERCHE("]";CELLULE("nomfichier";$BDD.A2);1));DROITE(CELLULE("nomfichier";$BDD.A2);NBCAR(CELLULE("nomfichier";$BDD.A2))-CHERCHE("$";CELLULE("nomfichier";$BDD.A2);1)))

Je vois ça demain au boulot. Si ça ne marche pas, je laisse tomber, j'ai déjà passé trop de temps là-dessus.
Si ça marche, je changerai le sujet en Renvoie du nom de feuille dans Calc et Excel, c'est plus proche du problème non ?
Bon maintenant au dodo !
Cordialement
Luke

[Jeff]

Merci Jeff pour la machine à gaz

De rien

En fait, j'imaginais plus le truc suivant :
- sur la feuille BDD, une fonction INFO("version") qui est en théorie commune à tous les tableur, par exemple en D1, englobée dans une fonction SI pour renvoyer 1 ou 0 selon si on est avec Calc ou pas.
- pour les autres formules, se baser sur BDD.D1 :

Code : Tout sélectionner

=SI(BDD.$D$1;extraction Excel;extraction LibO)

En revanche, pour les fonctions INDIRECT, soit tu combine le texte en te servant aussi de BDD.D1 :

Code : Tout sélectionner

=INDIRECT("BDD"&SI(BDD.$D$1;"!";".")&"A5")

Soit tu utilises ADRESSE (à l'intérieur d'INDIRECT) pour obtenir le séparateur de feuille correct.

Bref, une bonne occasion d'homogénéiser les suites bureautiques de ton parc

[luky-luke]

Bonjour,
On s'approche de la solution.
Ma formule à rallonge donnée dans mon message précédent pour extraire le nom de la feuille fonctionne. Glop !

utilises ADRESSE (à l'intérieur d'INDIRECT)

ça aussi c'est Glop !
Je laisse tomber le test INFO("version) pour le moment.
Et puis y'a quand même un truc à éclaircir entre: créer un fichier sous Excel avec l'extension .XLSX et le même créer avec LibO avec l'extension .XLSX en utilisant les mêmes fonctions. J'ai un fichier qui fonctionne sans adaptation de formule, étonnant non ? ou c'est moi qui déraille, c'est pas impossible

Je coche [Résolu] la solution est là, mais ne verrouillez pas le fil, je reviens avant la fin de l'année
Bref ! je vais prendre mon temps, tester et venir compléter ce fil avec un fichier ou deux, histoire d’essayer de faire le tour de la question.
Cordialement
Luke

[luky-luke]

Bonjour,
Pour faire suite à ce fil Nom feuille et fonction INDIRECT dans Calc et Excel

On va s'intéresser à la fonction CELLULE pour renvoyer soit l'URL avec le nom d'une feuille, soit que le nom d'une feuille. L'utilisation de la fonction est la même sous Calc et Excel


Pour un fichier créé avec Calc on pourra écrire

Code : Tout sélectionner

=CELLULE("filename")

ou

Code : Tout sélectionner

=CELLULE("nomfichier")

Les deux formules nous renvoient l'URL complète. L'argument "filename" sera interprété correctement sous Excel.
Pour un fichier créé avec Excel (en français) il n'accepte que la formule

Code : Tout sélectionner

=CELLULE("nomfichier")

Les résultats de la fonction pour un fichier créé
Avec Calc et enregistré avec l'extension .ods ou .xlsx:

Code : Tout sélectionner

'file:///LeCheminDe/MonRépertoire/MonFichier.ods'#$Feuille1

Avec Excel et l'extension .ods ou .xlsx

Code : Tout sélectionner

file:///LeCheminDe/MonRépertoire/[MonFichier.ods]Feuille1

A noter que avec Excel, le fichier enregistré gardera le séparateur ] et ne changera plus, même si l'on revient avec l'extension .ods, fichier ouvert et Enregistré sous avec Calc.

Avec Calc le nom de la feuille est précédé du séparateur $ et chez Bill(excel), c'est [ ] qui "encadre" le nom du fichier.
Pour renvoyer dans une cellule le nom de la feuille avec un fichier crée avec Calc, on utilisera donc la Formule :

Code : Tout sélectionner

=DROITE(CELLULE("filename";NBCAR(CELLULE("Filename)-CHERCHE("$";CELLULE"filename";1))

En principe Excel devrait interpréter le séparateur $, mais il ne le fait pas, il nous affichera #VALEUR !. La fonction imbriquée CHERCHE ne trouve pas le signe $, forcement

Donc pour que cela fonctionne à tous les coups, et quelque soit les manipulations future, on utilisera la formule :

Code : Tout sélectionner

=SIERREUR(DROITE(CELLULE("nomfichier");NBCAR(CELLULE("nomfichier")-CHERCHE("]";CELLULE("nomfichier");1));DROITE(CELLULE("nomfichier");NBCAR(CELLULE("nomfichier"))-CHERCHE("$";CELLULE("nomfichier");1)))

Les différents essais ont été effectués avec LibreOffice, il y a peut-être une interprétation différente du séparateur de feuille ] avec OpenOffice, n'ayant plus de suite OpenOffice sous la main, je n'ai pas fait de test
En espérant avoir été claire dans la présentation et même si je ne suis pas persuadé de l'utilité de ce fil.
En pièce jointe 2 fichiers. Les deux ont été créé avec Calc. Le fichier nommé Calc_Excel.xlsx à été ouvert avec Excel et Enregistré sous .xlsx. Le résultat qui nous intéresse est sur la feuille BDD.
Cordialement
Luke