INDIRECT()

[andré]

Bonjiur à vous tous,

A mon tour de vous poser une quetsion !

Lorsqu'on écrit une formule 'normale' se référant à une autre feuille, du type :
=Feuil1!A1
et qu'on modifie le nom de l'onglet de la Feuil1, XL modifie automatiquement ce nom dans la formule.

Mais lorsqu'on écrit une formule du type :
=INDIRECT('Feuil1!'&A1)
Xl ne modifie pas ce nom, puisque c'est du texte.

Je suis parvenu à contourner ce problème en me référant au contenu d'une autre cellule dans laquelle je récupére le nom de l'onglet par une formule du type :
=STXT(CELLULE('adresse';Feuil1!A1);CHERCHE(']';CELLULE('adresse';Feuil1!A1))+1;CHERCHE('!';CELLULE('adresse';Feuil1!A1))-CHERCHE(']';CELLULE('adresse';Feuil1!A1)))
En modifiant le nomle l'onglet, la formule s'adapte.

Mais même en nommant des parties de la formule cela reste toujours une 'usine à gaz', et puis il y a aussi cette cellule supplémentaire !

Quelqu'un aurait-il une procédure plus simple ?

Â+

 

[andré]

Rebonjour,

Pour supprimer la cellule supplémentaire, j'ai trouvé.

Il suffit d'incorporer directement la formule à rallonge sous la fonction INDIRECT() et cela marche.

Il ne me reste plus qu'à essayer de raccourcir cette formule, mais cela m'étonnerait (lol).

Â+

 

[Gael]

Bonjour André,

Je te livre les résultats de mes quelques essais.

Comme je n'ai pas réussi à faire fonctionner ta formule, j'ai récupéré celle de Laurent longre qui marche très bien:

=INDIRECT(STXT(CELLULE('nomfichier';Test!A1);TROUVE(']';CELLULE('nomfichier';Test!A1))+1;32)&'!'&A1)

le résultat est le même que =INDIRECT('Test!'&A1) mais on peut renommer la feuille.

Si l'on écrit =INDIRECT(Test!A1), on peut renommer la feuille mais le résultat n'est pas le même car si je suis sur Feuil1 et que A1 contient D3, A1 prendra la valeur D3 et on ira chercher la valeur de D3 dans la feuille Test

Avec =INDIRECT('Test!'&A1), on cherchera la valeur de A1 dans la feuille Test.

Je sais que tu préfère utiliser les fonctions standards d'XL mais sinon avec Morefun, on peut écrire:

=INDIRECT(NOMFEUILLE(2)&'!'&A1)

Bonne journée.

@+

Gael

Message édité par: Gael, à: 15/09/2005 15:59

 

[Pyrof]

Bonjour,

Sin on donne un nom à la cellule (pour exemple toto)

la formule devient INDIRECT(toto)

Si on renome l'onglet il n'y a pas de problème


Moi j'utilise au maximun le principe de nom de cellule

Ainsi si on modifie une feuille on a pas à modifier le vba

 

[andré]

Salut Gael et Pyrof, ainsi qu'à tous les autres qui suivent ce fil sabs ttop bien comprendre de quoi on parle (lol),

Pour Gael : je teste, la formule est plus courte (ce que je cherchais) et je te tiens au courant.
Mais ceci ne m'empêche pas de déjà te remercier de ta peine !

Pour Pyrof : merci à toi aussi, mais j'ai oublié (mille excuses) de signaler que je voulais incrémenter la formule, ce qui ne marche évidemment pas en nommant une cellule.

Je fais un exemple avec les diverses solutions et vous le mettrai en pature (on voit que j'habite la campagne - relol).

Â+

 

[laM]

Bonjour @Tous

Une variante possible pour construire une adresse est la fonction… :
ADRESSE(ligne ;colonne ;absolue/relatif… ;mode A1 ou L1C1;nom de la feuille)
Qui renvoie une adresse directement utilisable par INDIRECT.

Un exemple sera plus parlant, si :
A1=Feuil2
et
A2=ADRESSE(1 ;1 ;4 ;VRAI ;$A$1)
(ligne :1 ;colonne :1 ;adresse relative :4 ;Vrai : format A1 ; $A$1 : Feuil2)
Alors
A2 prend la valeur Feuil2!A1
INDIRECT(A2) donne le contenu de Feuil2!A1.

En rajoutant la fonction COLONNE() ou LIGNE() en fonction des besoins, on a l’incrément nécessaire pour une recopie de cellule.
Par exemple en recopiant « à droite » la formule :
A3=INDIRECT(ADRESSE(COLONNE();1;4;;$A$1))
les cellule A3, B3, C3, D3 vont devenir respectivement, le contenu des cellules :
Feuil2!A1, Feuil2!A2, Feuil2!A3, Feuil2!A4 (une sorte de =TRANSPOSE())

Il n’y a plus qu’à changer le contenu de A1 (nom de la feuille)
A1=Test
et
les cellule A3, B3, C3, D3 vont devenir respectivement, le contenu des cellules :
Test!A1, Test!A2, Test!A3, Test!A4

Voilà Maître André, j’espère qu’avec cela vous trouverez matière à réduire votre première formule
(cela me ferait bien plaisir de vous rendre service, car j’ai appris pas mal de chose en lisant vos solutions. Donc : Merci à vous)

Au revoir, à bientôt.

P.S. un bonjour et merci en particulier à Gaël, pour l’astuce de la couleur d’écriture identique à la couleur du fond dans la MFC pour masquer le texte (cf. Le planning coloré de Céline)
[file name=LeRenomDAndre.zip size=3839]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/LeRenomDAndre.zip[/file]

 

[andré]

Bonjour,

Merci laM pour ta procédure, mais elle ne résoud pas mon problème.

En effet il faut passer par une autre cellule (A1) qui reprend la nom de la feuille.
Pour automatiser la modification de ce nom en A1, à chaque modification du nomde l'onglet, il faut passer par une formule.
Alors tant placer cette formule dans la fonction INDIRECT().
Ton idée était celle que j'avais au départ, mais elle ne me plaisait pas.

La formule de Gael est plus courte que celle que j'avais employée.
Le comble est que je l'avais déjà employée sous un autre programme, mais elle ne m'était pas revenuie à l'esprit (Alhzeimer ?) !

Ma formule finale devient donc :
=INDIRECT(STXT(CELLULE('nomfichier';JANV!A1);TROUVE(']';CELLULE('nomfichier';JANV!A1))+1;32)&'!'&CAR(COLONNE()+64)&LIGNE())
Elle s'incrémente parfaitement et permet de changer le nom de l'onglet.

Merci à tous de votre peine.
A titre de revanche.
Â+

 

[JeanMarie]

Bonjour André

J'ai sans doute rien compris à ton problème, remarque c'est pas la première fois.

Je ne te propose pas cette formule, qui ne devrait pas pouvoir résoudre ton problème.
=DECALER(JAN!A1;LIGNE()-1;COLONNE()-1)

Je voulais simplement te faire un petit coucou.

@+Jean-Marie

 

[andré]

Salut JeanMarie,

Enfin je comprends la signification du '+' devant ton pseudo.
C'est une réference à la qualité de ta réponse (lol) !
Pour moi, tu peux en faire un doublon, ou mieux, je te propose : +^+

Effectivement, c'est 'THE' solution, pour autant toutefois de mettre A1 en valeur absolue.

Cela résoud par la même occasion un autre problème, quelque peu trop difficile à expliquer ici en détail :
Avec la fonction INDIRECT() je devais taper un espace dans les cellules vides pour que cela fonctionne correctement.
Avec le DECALER(), plus besoin.

Un grand merci à toi.
Â+

 

[Sylvie]

Bonjour Jean Marie, André, le forum

je passe juste applaudir à mon tour Jean Marie. Clap, Clap, Clap Monsieur @+ !!
Tu es dans une forme étincelante !! Cà promet pour le 24

Bonne journée à tous

 

[andré]

Salut Sylvie,

Tu parles des 24 heures de Lyon ?

Après Le Mans et Francorchamps, il se pourrait bien que cela devienne un rendez-vous incontournable.

Alors, plein gaz ... mais attention, il y a des chambres double (lol).

Â+

 

[JeanMarie]

Re

Merci André et Sylvie pour ces compliments.

Je reviens ici, pour vous signaler qu'il faut faire attention avec la définition des références sous Excel. En effet, notre tableur favori rajoute deux symboles ' pour signaler que le nom de fichier ou de la feuille contient un espace. En utilant la formule avec INDIRECT indiquée par André, il est préférable de remettre un ' dans le champ texte (ces symboles ne posent aucun problème).

=INDIRECT('''&STXT(CELLULE('nomfichier';JANV!A1);TROUVE(']';CELLULE('nomfichier';JANV!A1))+1;32)&
''!'&CAR(COLONNE()+64)&LIGNE())

@+Jean-Marie

 

[andré]

Resalut @+^+,

Merci pour ce complément d'information, bien que je ne place jamais d'espaces dans des noms d'onglets.

D'après mon psy, c'est probablement un résidu de l'époque où j'étais encore jeune, lorsque je me mettais à fréquenter les filles.
Je me souviens qu'elles me disaient souvent : Pourquoi laisses-tu un espace devant ton onglet (lol) ?

Va-t-on savoir où est la vérité !

Â+

 

[Sylvie]

Re bonjour André, Jean Marie, Le Psy, les autres, les espaces, les onglets,

André, rassure moi !!! ton psy et les filles dont tu parles, ils étaient belges au moins ?

@+

 

[Gael]

Bonjour à tous,

Juste pour faire aussi un compliment à jean-Marie, avec un peu de retard, le temps de tester cette formule très astucieuse.


@+

Gael