formule où le numéro de la ligne change(SOMME(dép1:dep2!M & ligne() )

[MaraMa]

Bonjour, pourriez-vous svp m'aider?
Mon problème c'est que je veux conserver dans mon fichier la forme (SOMME(dép1:dep2!Celulle) , car si j'insère un onglet entre les deux onglets déjà présents, la formule permettra de faire la somme des chiffre de ces 3 onglets.
En même temps, il est très probable d'ajouter les lignes à l'ENDROIT identique dans tous les onglet(c'est à dire j’ajoute une ligne 6 dans tous les onglets à identique)
Mon problème c'est que quand j'insère une ligne dans l'oglet contenant la somme des autres onglets, la formule ne change pas la cellule en adéquation avec la ligne ou elle se place après l'insertion d'une autre ligne. Est-qu'il y a un moyen de le contourner? faire une sorte de formule (SOMME(dép1:dep2!M & ligne() ) ou M est la colonne que je veux et ligne est celle où se trouve la formule...
Ci-joint le fichier d'exemple

 

[Gorfael]

Re : formule où le numéro de la ligne change(SOMME(dép1:dep2!M & ligne() )

Salut MaraMa et le forum
Pas envie de me fatiguer, alors juste une idée :
Dans une formule, si tu écris : =dep2!M & ligne() => Excel ne voit que du texte. Il faut dont lui indiquer que c'est une référence : C'est à ça que sert la fonction Indirect()
dep2!M & ligne() => Excel le comprend comme "dep2!M & ligne()"
Indirect("dep2!M" & ligne()) => excel le comprend comme la cellule de la feuille "dep2", colonne M, ligne qui contient la formule.
A+

 

[david84]

Re : formule où le numéro de la ligne change(SOMME(dép1:dep2!M & ligne() )

Bonjour,
j'ai revalidé la formule chez moi et pas de problème : toute modification apportée est prise en compte dans la formule 3D (cf.fichier, zone en jaune).
A+

 

[MaraMa]

Re : formule où le numéro de la ligne change(SOMME(dép1:dep2!M & ligne() )

Gorfael, merci pour ton aide, j'ai compris comment cela marche. Mais pour l'instant je n'arrive pas à l'intégrer comme il faut... je ne veux pas faire : somme(INDIRECT("DHG000!J"&LIGNE())) + somme(INDIRECT("DHG000!J"&LIGNE())) car je perd l'effet de la formule (SOMME(dép1:dep2!M..... qui va me prendre automatiquement un nouvel onglet si j'ai besoin d'en ajouter dans la somme.

david84, peux-tu stp me dire c'est quoi la formule 3D... (je travaille avec excel 2003, peut-être elle est pas encore là?) sinon dis-moi comment le trouver dans excel 2007? je suis très reconnaissante pour ta réponse!

 

[david84]

Re : formule où le numéro de la ligne change(SOMME(dép1:dep2!M & ligne() )

Re

david84, peux-tu stp me dire c'est quoi la formule 3D... (je travaille avec excel 2003, peut-être elle est pas encore là?) sinon dis-moi comment le trouver dans excel 2007? je suis très reconnaissante pour ta réponse!

Je ne pense pas qu'il y ait de problème sous Excel 2003, il te faut simplement valider correctement la formule :
- aller dans ta feuille récapitulative et rentrer=SOMME(
- aller dans ton 1er onglet et sélectionner la cellule voulue
- appuyer sur le touche Maj et sélectionner le dernier onglet puis la cellule voulue
- relâcher la touche Maj et valider par Entrée.
Sinon fais une recherche sur le Net en tapant Excel formule 3D et tu devrais trouver ton bonheur.
A+

 

[BOISGONTIER]

Re : formule où le numéro de la ligne change(SOMME(dép1:dep2!M & ligne() )

Bonsoir,

Sur cette version, la somme se fait en fonction de libellés(l'ordre des lignes n'a pas d'importance)
L'insertion de lignes dans un seul onglet ne pose pas de problème.

Somme 3D conditionnelle sur un champ

JB

 

[MaraMa]

Re : formule où le numéro de la ligne change(SOMME(dép1:dep2!M & ligne() )

David84,
Je connais cette démarche, mais je ne savais pas que cela s'appelle 3D.
J'ai exactement ce que j'avais fait.

Le problème c'est que j'ai ce fichier composé de 3 onglets identiques du point de vue de structure (le même nombre de lignes, colonnes...), dont un contient des formules SOMME de cellules identiques dans les deux autres onglets.

Il peut se faire que je dois ajouter une ligne quelques part au milieu.
J'ai fait un macro que fait les actions suivantes:

dans le premier onglet dép1bis:
Insert une ligne (par exemple au niveau de la ligne 6 ),
Copie une ligne au dessous et la colle avec les formules (recherchev)à la ligne 6 que je vient d'insérer=> ok

dans le deuxième onglet dep2bis:
Insert une ligne (au niveau de la ligne 6 ),
Copie une ligne au dessous et la colle avec les formules (recherchev)à la ligne 6 que je vient d'insérer=> ok

dans le troisième onglet TOTAL, celui ou j'ai la formule de Somme des cellule des autres:
Insert une ligne (au niveau de la ligne 6 ),
Copie une ligne au dessous et la colle avec les formules (SOMME 3D)à la ligne 6 que je vient d'insérer=> NOT OK, les cellule ne sont pas incrémentées dans l'ordre normal: ce que j'obtiens:
avant l'action
sur la ligne 5=SOMME(dép1bis:dep2bis!I5)
sur la ligne 6=SOMME(dép1bis:dep2bis!I6)

après l'insertion d'une ligne
sur la ligne 5=SOMME(dép1bis:dep2bis!I5)
sur la ligne 6 vide
sur la ligne 7=SOMME(dép1bis:dep2bis!I6)

après copie-colle
sur la ligne 5=SOMME(dép1bis:dep2bis!I5)
sur la ligne 7=SOMME(dép1bis:dep2bis!I5)
sur la ligne 6=SOMME(dép1bis:dep2bis!I6)

Je ne comprend pas pk au moment d'insertion la ligne 6 qui devient la ligne 7, la référence de la celulle reste I6 et non pas I7 une fois que la ligne est déplacée...
Copie-cole ne marche pas avec cette formule SOMME 3D....
Tu vois mon problème?

Merci...

 

[MaraMa]

Re : formule où le numéro de la ligne change(SOMME(dép1:dep2!M & ligne() )

Cher JB Boisgontier,

Je te remercie beaucoup pour cette information!!! Je ne pouvais même pas imaginer qu'il y a tant de choses intéressantes qu'on peut faire avec 3D! Le lien que tu as envoyé est très utile!

Aussi pour les fichiers joints, je pense utiliser plutôt la 3V2 car j'aurai besoin des onglets "totaux" à partir des groupes des onglets différents.

Et si j'ai bien compris, la 3V1 prend tous les onglets contenus dans le classeur...

Je te remercie!
Cordialement,
M