Ajout de lignes - suivi
Bonsoir,
j'ai développé un tableau excel (tableau de bord de suivi financier). J'ai créé des échéancier, des suivi en temps réel des budgets et enfin, un suivi des prévisions des besoins en budgets (c'est ce dernier qui pose problème).
en bout de ligne, j'indique l'onglet dans lequel se trouve les données, mais quand j'insère une ligne dans l'onglet en question, la formule dans les colonnes "sommes payées" ne se mets pas à jour et tout cela débouche assez rapidement à des erreurs du style #valeur.
Voici la formule en question :
=SI(O9="";"";SOMMEPROD((INDIRECT(O9&"!$B$18"):INDIRECT(O9&"!$B$26")=$A9)*(INDIRECT(O9&"!$K$18"):INDIRECT(O9&"!$K$26")=1)*(INDIRECT(O9&"!$M$18"):INDIRECT(O9&"!$M$26")=$A$6)*(INDIRECT(O9&"!$C$18"):INDIRECT(O9&"!$C$26")))+SOMMEPROD((INDIRECT(O9&"!$B$18"):INDIRECT(O9&"!$B$26")=$A9)*(INDIRECT(O9&"!$K$18"):INDIRECT(O9&"!$K$26")=2)*(INDIRECT(O9&"!$M$18"):INDIRECT(O9&"!$M$26")=$A$6)*(INDIRECT(O9&"!$C$18"):INDIRECT(O9&"!$C$26")))+SOMMEPROD((INDIRECT(O9&"!$B$18"):INDIRECT(O9&"!$B$26")=$A9)*(INDIRECT(O9&"!$K$18"):INDIRECT(O9&"!$K$26")=3)*(INDIRECT(O9&"!$M$18"):INDIRECT(O9&"!$M$26")=$A$6)*(INDIRECT(O9&"!$C$18"):INDIRECT(O9&"!$C$26"))))j'ai souligné les valeurs qui doivent évoluer mais comme il s'agit d'une valeur imposée (car entre " "), l'intégration de lignes dans l'onglet n'est pas reporté dans la formule.
A t on un autre moyen d'utiliser la fonction indirect () qui se met à jour quand on insère une ligne.
Je souhaiterais obtenir un truc du genre :
Avant insertion de ligne : =SI(O9="";"";SOMMEPROD((INDIRECT(O9&"!$B$18"]):INDIRECT(O9&"!$B$26")=$A9)*(INDIRECT(O9&"!$K$18"):INDIRECT(O9&"!$K$26")=1)*(INDIRECT(O9&"!$M$18"):INDIRECT(O9&"!$M$26")=$A$6)*(INDIRECT(O9&"!$C$18"):INDIRECT(O9&"!$C$26")))+ ...
Après insertion d'une ligne entre les lignes 18 et 26 : =SI(O9="";"";SOMMEPROD((INDIRECT(O9&"!$B$18"]):INDIRECT(O9&"!$B$27")=$A9)*(INDIRECT(O9&"!$K$18"):INDIRECT(O9&"!$K$27")=1)*(INDIRECT(O9&"!$M$18"):INDIRECT(O9&"!$M$27")=$A$6)*(INDIRECT(O9&"!$C$18"):INDIRECT(O9&"!$C$27")))+ ...
Je vous joins le fichier pour voir ce qui a déjà été fait et faire les test.
Merci par avance.
Cordialement.
Bonjour
Pas trouvé de solution : Désolé
Juste raccourci les formules (si je ne me suis pas planté)
Bonjour,
merci quand même d'avoir essayé. Merci aussi pour l'astuce pour réduire les formules. C'est vrai que je n'y avais pas pensé.
Cordialement.
Bonjour lio-m, Banzai64,
Peut être en corsant "indirect".
En partant du fichier de Banzai64.
Amicalement
Bonjour haonv,
qu'entendez vous par corsant "indirect"? En ouvrant le fichier joint, je ne vois pas de différences avec celui de banzaï 64.
Pouvez vous m'expliquez votre solution?
Cordialement.
Re ,
Par exemple ,
INDIRECT(O9&"!$B$18"):INDIRECT(O9&"!$B$26")=$A9)
devient
INDIRECT(O9&"!$B$18"):INDIRECT(O9&"!$B$"&INDIRECT(O9&"!$A$5"))=$A9)
Avec la formule en A5 sur la feuille "ATMO_et_Estair" , la matrice sera actualisée.
Sur le fichier joint au premier post ,si tu rajoutes ou supprimes des lignes,le résultat est toujours bon.(Sauf erreur de ma part)
Amicalement
Bonsoir,
tu as raison sur le principe de faire le calcul dans chaque feuille. Cependant, je n'arrive pas à voir où tu veux en venir avec ta solution et en quoi le problème est résolu.
Cependant, ton idée de tout calculer sur chaque feuille est plutôt bonne. Je vais faire les calculs en masqué dans chaque feuille (dans les premières lignes car je sais qui si on rajoute une ligne, ces dernières ne varieront pas). Il ne me restera alors qu'à aller chercher le résultat dans chaque feuille.
Qu'en penses tu car je ne vois pas quelle formule mettre en A5 dans la feuille "ATMO_et_estair"?
Merci pour ton aide.
Re ,
On ne doit pas bien se comprendre:
je ne vois pas quelle formule mettre en A5 dans la feuille "ATMO_et_estair"?
Sur la feuille "ATMO_et_estair" en A5 j'ai déjà mis la formule.
Celle ci renvoie le numero de la derniere ligne que tu veux récupérer .
Sur le fichier exemple que je t'ai renvoyé, la formule en A5 :
=EQUIV("Marché de base et avenants (HT) :";$C:$C;0)-3
renvoie "26".
Si tu ajoutes une ligne elle te renverra 27 ,les matrices pour sommeprod seront donc modifiés.
Sur ta feuille "prévisions", j'ai modifié les formules comme décrits sommairement précédemment.
tu as raison sur le principe de faire le calcul dans chaque feuille
Je ne fais pas le calcul dans chaque feuille:Il n'y a qu'une formule en A5
Il ne me restera alors qu'à aller chercher le résultat dans chaque feuille
Les formules que j'ai posté précédemment s'en chargent déjà.
J'ai refait des tets ici.Si je rajoute des lignes sur la feuille "ATMO_et_estair", je retrouve le même résultat.
Ou alors ,je n'ai rien compris à ce que tu voulais...
Amicalement
Bonjour haonv,
je n'avais pas vu là où tu voulais en venir. Je comprends mieux avec ta procédure détaillée.
Tu as bien compris ce que je souhaitais, c'est à dire mettre à jour le numéro de ligne dans les formules contenues dans la feuille prévision lorsque je rajoute une ligne dans le feuille "ARTKEA_et_Estair".
Je vais mettre tout cela en place et modifier mon fichier excel.
Je te remercie pour ton aide.
Amicalement.
-- 17 Jan 2011, 10:14 --
Bonjour haonv,
ta formule fonctionne nickel. C'est exactement ce que je voulais faire.
Cependant, excel considère que la formule est trop longue. J'ai donc utilisé l'optimisation de la formule de banzai64 afin d'obtenir ce qui suit (cellule B9 de la feuille prévisions) :
=SI($O9="";"";SOMMEPROD((INDIRECT(O9&"!$B$18":"$B$"&INDIRECT(O9&"!$A$5"))=$A9)*(INDIRECT(O9&"!$K$18":"$B$"&INDIRECT(O9&"!$A$5"))>0)*(INDIRECT(O9&"!$K$18":"$B$"&INDIRECT(O9&"!$A$5"))<4)*(INDIRECT(O9&"!$M$18":"$B$"&INDIRECT(O9&"!$A$5"))=$A$6)*(INDIRECT(O9&"!$C$18:"$B$"&INDIRECT(O9&"!$A$5")))))Mais j'ai un message d'erreur et je n'arrive pas à trouver ce qui cloche (j'imagine que c'est une histoire de guillemets en plus ou en moins).
Aurais tu une idée?
Merci.
-- 17 Jan 2011, 10:15 --
Bonjour haonv,
ta formule fonctionne nickel. C'est exactement ce que je voulais faire.
Cependant, excel considère que la formule est trop longue. J'ai donc utilisé l'optimisation de la formule de banzai64 afin d'obtenir ce qui suit (cellule B9 de la feuille prévisions) :
=SI($O9="";"";SOMMEPROD((INDIRECT(O9&"!$B$18":"$B$"&INDIRECT(O9&"!$A$5"))=$A9)*(INDIRECT(O9&"!$K$18":"$B$"&INDIRECT(O9&"!$A$5"))>0)*(INDIRECT(O9&"!$K$18":"$B$"&INDIRECT(O9&"!$A$5"))<4)*(INDIRECT(O9&"!$M$18":"$B$"&INDIRECT(O9&"!$A$5"))=$A$6)*(INDIRECT(O9&"!$C$18:"$B$"&INDIRECT(O9&"!$A$5")))))Mais j'ai un message d'erreur et je n'arrive pas à trouver ce qui cloche (j'imagine que c'est une histoire de guillemets en plus ou en moins).
Aurais tu une idée?
Merci.
Bonjour
A mon avis
Il y avait trop de guillemets
=SI($O9="";"";SOMMEPROD((INDIRECT(O9&"!$B$18:$B$"&INDIRECT(O9&"!$A$5"))=$A9)*(INDIRECT(O9&"!$K$18:$B$"&INDIRECT(O9&"!$A$5"))>0)*(INDIRECT(O9&"!$K$18:$B$"&INDIRECT(O9&"!$A$5"))<4)*(INDIRECT(O9&"!$M$18:$B$"&INDIRECT(O9&"!$A$5"))=$A$6)*(INDIRECT(O9&"!$C$18:$B$"&INDIRECT(O9&"!$A$5")))))Effectivement, il y avait trop de guillemets. Ca fonctionne nickel maintenant.
Merci Banzai64 et haonv pour votre soutien et vos conseils.
Amicalement.
Lio_m.
-- 17 Jan 2011, 11:26 --
J'ai posté un premier message qui n'apparaît pas dans le suivi du sujet.
La dernière formule de Banzai64 marche nickel (avec 2 ou 3 changements de lettres). Effectivement, il y avait trop de guillemets.
Merci haonv et banzai64 pour votre soutien et vos conseils. Le sujet est résolu.
Amicalement.
Lio_m.
Bonsoir lio_m ,Banzai64 ,
Je ne comprenais pas pourquoi Lio_m me parlait d'adapter la solution à la formule simplifiée de Banzai.
J'étais persuadé d'être parti de celle-ci.Je n'avais pas copié la bonne formule (bien vu l'adaptation ,Banzai64).
A bientot sur d'autres fils .
Amicalement