Combinaisons des fonctions MOYENNE et INDIRECT avec #REF! pour réponse

Bonjour à toutes et à tous.

Je cherche à réaliser, sur des feuilles portant la même mise en page, une moyenne en E5 sur toutes les cellules B5 des feuilles précédant la feuille active. Je pars donc toujours de la cellule Feuil1!B5 pour aller jusqu'à la cellule Feuil(n-1)!B5 si je suis en Feuil(n).

J'ai, en D5, une cellule comportant le nom de la feuille précédant la feuille active, obtenue grâce à la formule :

="Feuil"&STXT(CELLULE("nomfichier";A1);TROUVE("]";CELLULE("nomfichier";A1))+6;2)-1

Je pensais utiliser, pour ma moyenne en E5, la formule :

=MOYENNE(INDIRECT("Feuil1:"&D5&"!B5"))

Or cette formule me renvoie #REF! dans toutes les cellules qui la portent. Pire, en tapant la formule =MOYENNE(Feuil1:Feuil3!B5) en Feuil4!E5, qui fonctionne très bien, puis en progressant pas à pas et en remplaçant ensuite cette formule par =MOYENNE(INDIRECT("Feuil1:Feuil3!B5")), qui devrait de fait très bien marcher elle-aussi, j'obtiens de suite #REF!

Je ne comprends pas ce qui se passe, vu que les nombreuses fois ou le sujet a été abordé dans ce forum, les réponses proposées semblent indiquer une compatibilité entre les fonctions MOYENNE et INDIRECT.

Je joins un fichier "Test" pour illustrer plus clairement ma demande.

Cordialement.

Gaël

Bonjour Gaël,

=MOYENNE(INDIRECT("Feuil1:"&D5&"!B5"))

Essayez avec la formule ci-dessous

=MOYENNE(INDIRECT("Feuil1!B5:"&CONCATENER(D5;"!B5");1))

les deux fonctionnent chez moi

Bonjour njhub.

D'abord merci pour cette réponse rapide.

Même problème avec votre formule. De même qu'avec la formule :

=MOYENNE(INDIRECT("Feuil1!B5:"&D5&"!B5"))

qui devrait elle-aussi revenir au même.

J'en suis à me demander si le problème ne vient pas de mon logiciel Excel, et s'il n'y a pas un paramétrage à faire dans les options...

Une remarque : je n'avais encore jamais utilisé la fonction INDIRECT pour référencer des plages de cellules, et elle a toujours bien fonctionné avec des références à une seule cellule.

Gaël

J'ai les mêmes problèmes !! curieux ...

Je suppose que tu veux faire le cumul de plusieurs feuilles, dans ce cas mets le sur chaque feuille et ne tient compte que de la feuille-1

Essayez en remplaçant les points d'exclamation par des points

Même constat en remplaçant les points d'exclamation par des points.

Mais le fait que toutes les formules marchent chez vous ("njhub") semble indiquer que le problème ne vient pas des formules elles-mêmes.

Je vais creuser l'idée de Steelson, le "fils de l'acier", et tenter de mettre en place une itération avec la fonction SOMME reprenant seulement la page d'avant. Cela ne devrait prendre qu'une ou deux cellules de plus et maintenir l'emploi de la fonction INDIRECT sur une seule cellule.

Je vous tiens au courant.

Je pense que Calc est "en avance" sur Excel ... du moins plus cohérent.

Autre solution : faire un onglet récap, faire dessus le cumul et si besoin le rappeler dans la feuille ad-hoc

As-tu beaucoup de valeurs à sommer de cette façon de feuille à feuille ?

J'ai pour ma part été surpris que la formule sans la référence à B5 dans la feuille 1 fonctionne, là c'est aberrant pour moi

C'est à peu près ce que j'ai fait.

J'ai créé deux cellules sous la cellule valeur qui donnent la somme des valeurs et le nombre de feuilles.

Je joins le fichier ci-dessous.

Ces manips donnent le bon résultat semble-t-il.

Le problème de départ n'est pas résolu mais c'est une bonne méthode pour le contourner.

En tous cas merci à tous pour vos idées!

Et j'ai 30 feuilles à faire donc je réfléchis bien les premières...

Gaël a écrit :

Le problème de départ n'est pas résolu mais c'est une bonne méthode pour le contourner.

L'autre méthode est de faire une feuille Récap et de "dialoguer" qu'avec celle-ci.

Voici une variante dans laquelle tu peux changer le nom d'onglet !

En effet, ça marche bien aussi.

Tu peux même mettre une formule en A1 permettant d'avoir directement le nom de la feuille sans avoir à le retaper.

Je retiens l'idée en tous cas.

Merci!

Gaël a écrit :

Tu peux même mettre une formule en A1 permettant d'avoir directement le nom de la feuille sans avoir à le retaper.

il est préférable de ne pas faire trop de liens entre toutes feuilles et je préfère souvent faire un sommaire de navigation, même si ta version m'a quand même bluffé

Au passage, j'ai oublié de mettre des liens vers les onglets ad-hoc, nécessaire quand on en a 30 avec des noms assez longs !

un autre exemple avec des liens

Oui c'est bien le cas pardon. J'étais passé trop vite sur les cases A1.

Après ce n'est pas un souci pour les pages supplémentaires. J'avais surtout besoin de méthodes pour réaliser mon objectif et vous m'en avez proposé d'excellentes. Pour le reste je devrais me débrouiller, d'autant plus que je travaille en vérité sur un fichier bien plus lourd et qui doit faire beaucoup de choses, avec des paramètres dans tous les sens... J'avais fait le fichier "Test" uniquement pour aborder un souci particulier.

Je proposerai peut-être le fichier sur lequel je travaille véritablement afin de voir quelles formules plus simples pourraient être employées pour l'alléger un peu, mais quand je l'aurai terminé et plus pour ma culture excel personnelle.

ok