Problème de syntaxe avec INDIRECT et nom dynamique

Bonjour le forum,

Je me permets de vous soumettre un problème de syntaxe sur lequel je sèche.

J'avais la formule suivante reproduites plus d'une centaine de fois dans un fichier :

Code:

---

=INDEX(INDIRECT("'"&$AG$2&"'!C:C");EQUIV(1;(($A10=INDIRECT("'"&$AG$2&"'!A:A")))*(($A$1=INDIRECT("'"&$AG$2&"'!H:H")));0)+1)

---

Pour gagner en rapidité dans l'exécution, j'ai créé des noms via le gestionnaire de noms.

Ainsi, ColonneA fait référence à DECALER($A$1;1;0;NBVAL($A:$A)-1)
ColonneC fait référence à DECALER($C$1;1;0;NBVAL($C:$C)-1)
ColonneH fait référence à DECALER($H$1;1;0;NBVAL($H:$H)-1) ...


J'ai ensuite voulu faire :

Code:

---

=INDEX(INDIRECT("'"&$AG$2&"'!ColonneC");EQUIV(1;(($A10=INDIRECT("'"&$AG$2&"'!ColonneA")))*(($A$1=INDIRECT("'"&$AG$2&"'!ColonneH")));0)+1)

---

et j'ai un problème de #REF! dans la formule.

Quelqu'un pourrait-il me dire ce que j'ai raté ? Je pense qu'il doit y avoir des guillemets ou des & qui manquent, mais je ne vois pas où !

Merci d'avance pour votre aide.

Cordialement.

Bonjour,

Sauf erreur de ma part, je crois que dans ta formule :

Code:

---

=INDEX(INDIRECT("'"&$AG$2&"'!ColonneC");EQUIV(1;(($A10=INDIRECT("'"&$AG$2&"'!ColonneA")))*(($A$1=INDIRECT("'"&$AG$2&"'!ColonneH")));0)+1)

---

Il faudrait plutôt du :

Code:

---

=INDEX(INDIRECT("'"&$AG$2&"'!" & ColonneC & "");EQUIV(1;(($A10=INDIRECT("'"&$AG$2&"'!" & ColonneA & "")))*(($A$1=INDIRECT("'"&$AG$2&"'!" & ColonneH & "")));0)+1)

---

Je me trompe peut être mais c'est le premier truc qui m'a semblé bizarre dedans...

Cordialement,

Bonjour

En principe les formules nommées intègrent le nom de l'onglet dans les références sauf à les rédiger en mode tout onglet.

Quand tu valides cela ajoute le nom de l'onglet dans la formule.

DECALER renvoie le contenu d'une plage et non son adresse, d'où le non fonctionnement de INDIRECT (on le fait d'ailleurs dans l'assistant fonction qui permet de contrôler au fur et à mesure ou a posteriori la formule...

Concernant l'aspect dynamique : que contient $AG$2 ? La référence à une autre feuille ou à la feuille en cours ?

Bonjour Gado2600,

Merci pour ton aide, mais apparemment ce n'est pas ça. J'ai toujours le même problème.

Je suis allé voir si les noms dynamiques fonctionnaient bien : c'est le cas.

j'ai ensuite exécuté la formule en pas à pas et quand Excel arrive à ColonneC, j'ai une erreur !

Si tu as d'autres idées, je suis preneur.

A +

Citation:

---

Envoyé par 78chris

Concernant l'aspect dynamique : que contient $AG$2 ? La référence à une autre feuille ou à la feuille en cours ?

---

C'est vrai que ta formule finale est un peu bizarre... Quel est l'objectif de celle-ci ? N'y a-t-il pas moyen de la simplifier avec des sommeprod ou un autre truc du genre ?

Bonjour 78Chris,

$AG$2 contient la formule suivante :

Code:

---

=STXT(INDEX(Feuille;NBVAL(Feuille));CHERCHE("]";INDEX(Feuille;NBVAL(Feuille)))+1;31)

---

et fait référence à la dernière feuille de l'onglet.

J'ai fait ce tableau au fur et à mesure. C'est vrai que des SOMMEPROD fonctionneraient peut-être mieux. Il faut que je creuse.

A +

Re

Peux-tu être un peu plus explicite : la formule c'est bien mais sa finalité et ce qu'elle retourne aiderait à avoir une vue d'ensemble pour te donner une solution...

Rebonjour 78chris, Gado2600,

Désolé de ne pas vous avoir répondu plus tôt, mais j'anonymisais le fichier test que je vous envoie, ce qui est déjà tout un poème !

En fait, je cherche à récupérer, dans un tableau Excel, des données contenues dans une autre feuille en fonction de critères.

Il s'agit de comptes comptables et, en fonction de deux critères (X et Z), je veux aller récupérer les valeurs dans chacun des comptes, dans la bonne colonne (j'ai quatre colonnes : débits, crédits, cumuls débits, cumuls crédits).

Le problème supplémentaire est que je ne dois pas récupérer la valeur qui est x colonnes par rapport au compte, mais celle qui est x colonnes + la ligne en dessous.

Exemple, en A9 de la feuille Immos, j'ai le compte 20531. Je veux aller récupérer, dans la feuille Balance, les débits et crédits, ainsi que les cumuls des débits et des crédits pour les indicateurs X et Z. En résumé, voilà ce que ça donne :

Feuille Immos	Feuille Balance
B9	C265
C9	C64
E9	E265
F9	E64
I9	D265
J9	D64
L9	F265
M9	F64
...	...

Le fichier test n'est qu'un extrait, car en fait, dans la feuille Immos, j'ai 67 lignes de tableaux, parfois sur une trentaine de colonnes, en fonction des comptes.

Techniquement, ma formule fonctionne, mais elle est très très lente. Je pense que ça pourrait marcher avec des SOMMEPROD (si c'est tel compte, tel indicateur [X ou Z], telle colonne, tu prends telle valeur), mais il faudrait qu'il comprenne qu'il doit aller chercher la valeur de la ligne d'en-dessous.

Pour accélérer la vitesse d'exécution, j'ai supprimé les calculs automatiques. Mais ça ne suffit pas, car à partir d'une certaine ligne, il ne se passe plus rien. Excel "sature".

Voilà, vous savez tout. Au vu de tout ça, si l'un de vous peut m'aider ...

Merci d'avance.

Cordialement.

Bonjour

On peut simplifier les formules, éviter les matricielles et don accélérer le calcul.

Il suffit de trouver la 1ère ligne de la zone X par une formule dans la feuille Balance, puis 2 formules nommées : une pour la plage des X une pour la plage des Z et après on travaille avec de simple formules DECALER.


Question : la dernière feuille du classeur n'est-elle pas toujours Balance ?

J'attends ta réponse avant de te donner ces formules.