Références circulaire à cause de noms par feuille : comment s'en sortir ?

Bonjour à tous,

J'ai un fichier Excel pour gérer mes comptes, un truc que je peaufine année après année (et qui me permet de m'amuser avec Excel, surtout..?). Pour chaque nouveau mois, je duplique une feuille nommée "Template" et je la nomme du mois et de l'année (ex. : "septembre'19") de telle manière que la feuille est capable de comprendre quel mois elle est, d'aller récup les valeurs de mes comptes sur la feuille du mois précédent etc... Un truc rodé qui fonctionnait bien jusque là!

Parce que j'en avais marre des liens relatifs (ex. : ='Aout''19'!J33) qui nécessitent de tout modifier dès que je bouge une cellule de place, j'ai décidé de définir des noms par feuille (et pas par classeur) pour un certains nombre de cellule (ex. : "LivretA_Valeur"), ce qui me permet d'avoir pour chaque feuille une valeur nommée "LivretA_Valeur".

En complément, j'ai développé une petite fonction intitulée ValeurMoisPrecedent() qui me permet d'aller récupérer des valeurs pour ces noms (typiquement : "=ValeurMoisPrecedent("LivretA_valeur")") :

Function ValeurMoisPrecedent(Information) As Double

    OngletPrecedent = Range("Var_MoisPrecedent_mmmm").Value & "'" & Range("Var_MoisPrecedent_aa").Value

    ValeurMoisPrecedent = Sheets(OngletPrecedent).Range(Information)

End Function

où Var_MoisPrecedent_mmm est lui même un nom au niveau de chaque feuille contenant le nom du mois en français, Var_MoisPrecedent_aa un nom au niveau de chaque feuille contenant l'année en cours sur deux digits etc...

Le problème, c'est que dès que j'utilise cette fonction, j'ai l'impression que Excel pète un plomb et me met des erreurs circulaires sur énormément de cellules et pas seulement celles qui utilisent cette fonction!

"Pire", dès que j'ouvre un autre fichier en même temps que celui-ci, et bien une fois sur deux le calcul de la page se met en erreur avec des #Valeur partout, qui se résolvent d'elles même en faisant un simple double-clic + Entrée sur chaque cellule en #Valeur cellule (sans rien modifier dans son contenu..).

Ma question est donc la suivante : est-ce que j'aurais usé abusivement des noms par feuille et est-ce qu'il y aurait des limites du genre "On peut mettre des noms par feuille plutôt que par classeur mais ne pas utiliser les mêmes noms dans chaque feuille".. ?

C'est con, j'avais un truc que je trouvais vraiment flexible pour faire évoluer mon fichier (=abandonner les références brut de cellules) mais malheureusement ces références circulaires s'affichent trop souvent (pop-up) pour que je puisse garder mon fichier en l'état...

Si vous avez des idées pour m'en sortir, je suis preneur !

Bonjour,

Regarde si dans ta fonction il ne manquerait pas une apostrophe et/ou un point d'exclamation !

Mais OUI j'ai une idée simple pour que tu t'en sortes définitivement !

Abandonne les feuilles par mois ... et mets toutes les recettes et dépenses sur une seule et même feuille.

Puis fais des TCD pour avoir les bilans par mois.

Et là tu répondra pleinement à ton objectif

(et qui me permet de m'amuser avec Excel, surtout..?)

ah ah

Oui effectivement j'imagine que je pourrais reprendre le fichier dans un format totalement différent..

"Regarde si dans ta fonction il ne manquerait pas une apostrophe et/ou un point d'exclamation !" : je sais pas si c'est ironique ou pas mais dans le doute, j'ai regardé et il ne me semble pas manquer qqch. En tout état de cause, la fonction fonctionne correctement dans le sens où elle renvoie le résultat attendu.

En revanche, ce qui est étonnant, c'est quand j'autorise le calcul itératif (5 boucles par ex.), j'ai quelques cellules (3-4 cette fois-ci) utilisant cette fonction qui s'incrémente de manière un peu folle... je l'analyse mais je sais pas comment l'expliquer. Voici le comportement :

Feuille "Septembre'19", A1 : =ValeurMoisPrecedent("LivretA_valeur") (=qui récupère la valeur sur la feuille "aout'19")

Feuille "Octobre'19", A1 : =ValeurMoisPrecedent("LivretA_valeur")+200 (=qui récupère la valeur sur la feuille "septembre'19" et qui rajoute 200)

Là, si je met les Options, Formules, à "Calcul automatique", j'aurais "Références circulaires"

si je met les Options, Formules, à "Activer le calcul Itératif", puis "5", j'aurais une valeur Octobre'19!A1 qui va augmenter grandement, bien plus que 200 mais plus d'erreur de référence circulaire en revanche !

Alors on pourrait se dire que Septembre'19!A1 pointe sur Octobre'19!A1, et que Octobre'19!A1 pointe sur Septembre'19!A1, ça expliquerait la référence circulaire... mais en fait non! Chacun va bien lire la feuille du mois précédent..

C'est comme si la fonction qui doit normalement aller chercher sur chaque feuille la référence de la feuille précédente, s’emmêlait les pinceaux (ou n'arrivait pas à oublier les valeurs déjà lues des feuilles précédentes) et bouclait alors que le code a priori ne lui demande pas de boucler (ce qui expliquerait qu'une simple édition d'une cellule en #Valeur résolverait manuellement chaque cas : ça forcerait la fonction à bien relire quelle est la feuille précédente et à afficher la bonne valeur).

D'où une question peut être incongrue : peut-on forcer une fonction à libérer à chaque exécution sa mémoire pour s'assurer qu'à chaque nouvelle utilisation, elle ne conserve pas des valeurs précédentes ?

Et la toute première feuille, elle pointe sur quoi ?

"Regarde si dans ta fonction il ne manquerait pas une apostrophe et/ou un point d'exclamation !" : je sais pas si c'est ironique ou pas mais dans le doute, j'ai regardé et il ne me semble pas manquer qqch. En tout état de cause, la fonction fonctionne correctement dans le sens où elle renvoie le résultat attendu.

Non ce n'était pas ironique, mais j'ai été perturbé par le fait de voir un seul
"'"
dans ta fonction.

Or, quand on fait appel à un onglet avec des espaces comme XX YY, l'écriture est ='XX YY'!B2

Dans ton cas, je vois que tu as mis une apostrophe entre XX et YY, et mon excel me dit que dans ce cas l'écriture est ='XX''YY'!B2

D'où une question peut être incongrue : peut-on forcer une fonction à libérer à chaque exécution sa mémoire pour s'assurer qu'à chaque nouvelle utilisation, elle ne conserve pas des valeurs précédentes ?

Je ne sais pas, mais il faudrait résoudre le problème de fond.

Merci pour votre aide.

Les tildes dans le nom de l'onglet ? Oui je sais que c'est pas la meilleure idée, il faudra que je corrige ça avec le temps mais en tout état de cause, ils sont bien géré dans le fichier (mais pas des plus pratiques j'en conviens).

Poser mon problème sur ce forum a clarifié mes idées. C'est effectivement bien cette fonction qui met la grouille. Je l'ai remplacé par un INDIRECT directement dans la feuille et ça a totalement résolu le problème de référence circulaire.

Je pense donc que c'est bien la fonction qui s'y perd à force d'être utilisée dans chaque feuille à lire les même cellules etc...

Merci à vous pour votre aide !

Bonjour à tous

Et si tu mettais tes données sous forme de tableau ? Plus besoin de définir des plages nommées.

En nommant le tableau du nom de l'onglet on peut aussi faciliter le lecture...

Autre solution aussi ... puisque je suppose que toutes tes données commencent par un première date au même endroit, plus besoin de nommer une cellule particulière, prends appui sur la première date avec la fonction.

Si ta première date est en A5

=NOMPROPRE((TEXTE(FIN.MOIS(A5;-1);"mmmm'aa")))

te donnera le nom de l'onglet précédent.

Cela simplifie la gestion des noms.

Mais pense à mettre un chapeau sur Août !

Rechercher des sujets similaires à "references circulaire cause noms feuille comment sortir"