Mise à jour auto formules nommées

[rudaye]

Bonjour le forum,

ça faisait un timoment que j'étais pas venu.
Enfin bon, j'ai une question sur un ti problème que j'ai avec mes formules.
Mon classeur comporte 12 feuilles (mois de l'année),et une feuille qui calcul des indicateurs en exécutant des formules en VBA .

Sachant que ces formules en VBA vont chercher des infos se trouvant dans mes feuilles par mois qui contiennent des formules que j'ai nommées comme vous pourrez le voir dans mon fichier attaché.

Ce qui me dérange c'est que si par exemple, on ajoute ou supprime une ligne du mois d'octobre, la formule correspondant ne fait plus référence au bon nombre de ligne . Donc à chaque fois que j'insère ou retire une ligne il faut que je mette à jour toutes les formules de ma feuille à la main!!
Et comme c'est une appli qui sert au boulot, mes collègues ne connaissant pas VBA et excel assez pour le faire eux meme je voudrais que cela se fasse automatiquement.

J'ai du retirer des feuilles pour que le fichier fasse moins de 50Ko
Je ne sais pas si j'ai été bien clair?

Merci d'avance
[file name=QuestionForum.zip size=50837]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/QuestionForum.zip[/file]

 

[Dugenou]

Salut,

Ton fichier sans les feuilles est assez difficile à lire (beaucoup de formules donnent #REF)

Je ne trouve pas de formules nommées mais seulement des zones nommées.

Pourrais tu donner des explications dans le fichier sur ce qui ne va pas quand on enlève des lignes ?

Merci

 

[rudaye]

Bonjour et merci de répondre.

Alors ce qu'il se passe c'est que par exemple pour la feuille 'OCTOBRE' , je nomme certaines colonnes (ColUOctobre, ColGetOctobre etc...)

Et ensuite pour la feuille 'INDICATEURS', je veux répertorier chaque colonne où E1 ou E2 ou E3 etc ... sont cochés

Mais ce qu'il se passe c'est par exemple cette formule
Définit la colonne nommée 'U' de la feuille octobre :
=Octobre!$H$6:$H$16

par exmple si je rajoute une ligne au tableau de la feuille OCTOBRE, la formule ne devra plus être
=Octobre!$H$6:$H$16
mais celle la
=Octobre!$H$6:$H$17

Et donc les formules VBA de la feuille INDICATEURS me retournent #REF!

je sais pas si je me fais bien comprendre?

Merci d'avance

 

[Gael]

Bonjour Rudyperray, bonjour Dugenou,

En fait il faut définir des plages variables dont la longeur s'adapte en fonction du nombre de valeurs la formule:

=DECALER(Octobre!$H$6;;;NBVAL($H:$H)-1) permet de définir la colonne U de la feuille Octobre en fonction du nombre de valeurs de la colonne H (-1 à cause du titre). Si toutes les cellules de la colonne ne sont pas toujours remplies, tu peux aussi faire référence à une autre colonne pour le nombre de valeurs en remplacant $H:$H par $B:$B.

En regardant ton tableau d'indicateurs, j'ai aussi pensé que su les en-têtes de colonnes avaient le même nom que tes feuilles, on pourrait faire une formule unique qui va chercher les résultats dans la feuille correspondant à l'en-tête ce qui donnerait:

=DECALER(INDIRECT((IndicateursADR!K$5)&'!'&'$H$6');;;NBVAL($H:$H)-1)

IndicateursADR!K$5 donne le mois (en-tête de colonne) K pouvant varier de C à N et avec Indirect, on ajoute '!$H$6' et la formule a comme résultat:

=DECALER(Octobre!$H$6;;;NBVAL($H:$H)-1)

Une seule formule par colonne, ce qui pourrait vraiment simplifier ton tableau car tu écris ColU=7 et selon la colonne, XL va prendre le mois correspondant.

@+

Gael

 

[rudaye]

Bonsoir Gael et merci!

Je vais regarder ça et je te tiens au courant sur le fil merci et bonne soirée

 

[Gael]

Re,

Un petit oubli dans la formule nommée, pour que ça marche, il faut aussi utiliser la fonction Indirect dans la colonne du nombre de valeurs sinon la formule va prendre le nombre valeurs de la colonne H de la feuille Indicateurs. Ca donne:

=DECALER(INDIRECT((IndicateursADR!K$5)&'!'&'$H$6');;;NBVAL(INDIRECT((IndicateursADR!K$5)&'!'&'$H:$H'))-1)

C'est un peu compliqué, mais tu divise par 12 le nombre de formules nommées et tu simplifies toutes les formules de la feuille indicateurs que tu peux recopier à droite sans problème, la même formule étant utilisée pour tous les mois.

@+

Gael

 

[rudaye]

Bonsoir le forum, Gael,Dugenou


Gael, j'ai essayé la formule
=DECALER(Octobre!$H$6;;;NBVAL($H:$H)-1)
et c'est nickel!!

merci

Sinon par exemple si ma colonne est vide (ce qui arrive à chaque début ed mois) dans mon tableaux indicateur, il me retourne #REF!


Comment puis je faire pour qu'il m'affiche 'SO' lorsque mon tableau est vide?

MERCI d'avance

 

[Gael]

Bonsoir Rudy, bonsoir Dugenou,

Tu testes simplement le nombre de valeurs d'une colonne significative. Si l'on prend ColU que tu viens de définir, tu peux écrire dans ton tableau indicateurs:

=Si (NBVAL(ColUOctobre)=0;'SO';sommeprod...)

Une fois ton tableau OK, renvoie-moi un exemple et je ferai un essai pour toi avec les fonctions Indirect.

@+

Gael

 

[Dugenou]

Là : bravo Gael

J'avais tout vu sur ce coup la !

J'avais pas bien suivi non plus

Bonne continuation !

Message édité par: Dugenou, à: 18/10/2005 22:49

 

[Dugenou]

Là : bravo Gael

 

[rudaye]

Bonsoir le forum, Gael, Dugenou


Bon bah Gael j'ai suivi ton exemple
=Si (NBVAL(ColUOctobre)=0;'SO';sommeprod...)


mais de cette façon :
=SI(ESTERREUR(SOMMEPROD((ColUJanvier<>7)*(ColPanneJanvier='o')*((ColCreationJanvier='ý')+(ColRefonteJanvier='ý'))));'SO';SOMMEPROD((ColUJanvier<>7)*(ColPanneJanvier='o')*((ColCreationJanvier='ý')+(ColRefonteJanvier='ý'))))


@+
et merci pour tout (gael et dugenou)

bonne soirée

 

[Gael]

Bonsoir Rudy, bonsoir Dugenou,

Merci dugenou, devant ces compliments, je me suis senti obligé d'aller jusqu'au bout du raisonnement et de modifier le fichier avec les noms variables.

Ruddy, je t'envoie un fichier test, j'ai recréé tous les mois en copiant une des feuilles, modifié, les en-têtes de colonnes, les noms et les formules.

Avantages:

Il ne reste que très peu de noms définis.
les formules sont plus simples et recopiables d'une colonne sur l'autre
La macro de création de formules n'est plus utile.

dis-moi ce que tu en penses.

@+

Gael [file name=QuestionForum_20051019183501.zip size=50737]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/QuestionForum_20051019183501.zip[/file]

 

[rudaye]

Merci Gael!!

Je vais regarder ça calmement demain.

Le temps que je comprenne et que je l'applique à mon fichier au boulot!!


Merci beaucoup et bonne soirée à vous

 

[rudaye]

Je vous tiens au courant demain!!

 

[rudaye]

Bonsoir le forum, gael, dugenou

Gael j'ai regardé le fichier mais je ne comprend pas trop la formule :

par exemle:

=DECALER(INDIRECT((IndicateursADR!D$5)&'!'&'$D$6');;;NBVAL(INDIRECT((IndicateursADR!D$5)&'!'&'$D:$D'))-1)


tu peux m'expliqer?

Merci d'avance