Insertion ligne automatique et mise en forme

[Captaincake]

Bonjour, Leforum

Et merci pour toutes les aides que tu apportes.

J'ai parcouru le forum pour résoudre trouver des solutions mais je n'ai pas tout compris ni réussi les transposer à mon cas donc Encore une demande d’aide pour insérer une ligne automatiquement….

Voilà mon problème (j'utilise Excel 2007), je suis en train de créer une feuille pour suivre les kilomètres parcourus par une flotte de véhicules en location utilisés par différents services.
J’aimerais que lorsque la dernière (ou l’avant dernière) ligne de mon tableau contienne une valeur ou du texte, une ou deux lignes soient automatiquement insérées à la suite de mon tableau tout en gardant la mise en forme et en étendant les formules associées aux colonnes.

N’y connaissant rien en VBA, j’ai pensé à insérer une formule après la dernière ligne du genre « SI la dernière ligne contient une valeur ALORS Insérer ligne ou copier une ligne du tableau en dessous (pour conserver la mise en forme) ». Mais là non plus je n’y connais pas grand-chose et je n’y arrive pas…

Si quelqu’un peut m’expliquer la démarche à suivre.

Je vous remercie par avance.

 

[mth]

Re : Insertion ligne automatique et mise en forme

Bonjour Captaincake

Un truc tout simple qui je l'espère pourra t'aider, est d'utiliser l'outil "Tableau" d'Excel.
Sur Excel 2007, sélectionne ta zone de A17 à W23, puis dans le menu Accueil, clique dans le ruban sur "Mettre sous forme de tableau"
(choisis un modèle simple à ton goût que tu pourras modifier ensuite au cas où, et n'oublie pas de cocher la case "Mon tableau comporte des en-têtes")
Ensuite, tu verras que comme par magie, dès que tu saisis une donnée après la dernière ligne, elle est automatiquement intégrée à ton "Tableau", et de même, les formules ligne 15 s'ajustent toute seules sans avoir quoi que ce soit à faire

Bien à toi,

mth

 

[Captaincake]

Re : Insertion ligne automatique et mise en forme

Pourquoi faire compliqué quand on peut faire simple... Il fallait juste y penser

Je te remercie.

 

[Captaincake]

Re : Insertion ligne automatique et mise en forme

Bonjour,

Je reviens vous solliciter.

L'outil tableau a résolu mon problème cependant un autre se pose à moi.

J'aimerais protéger la feuille pour éviter qu'une formule ne soit effacée par inadvertance, jusque là pas de soucis, mais dès que la feuille est protégée, la magie du tableau n'opère plus et les données saisies après la dernière ligne ne sont plus automatiquement intégrées.

J'ai déverrouillé toutes les cellules et coché toutes les autorisations lors de la protection, rien n'y fait, plus de magie .

Y a t-il un moyen ?

 

[mth]

Re : Insertion ligne automatique et mise en forme

Bonjour Captaincake,

Effectivement, dans ce cas il va falloir passer par une macro.
Pas trop mon fort , je préfère laisser la main à un vrai VBAiste, mais je continue à suivre le fil.

A bientôt

Mth

 

[mth]

Re : Insertion ligne automatique et mise en forme

re

En attendant l'automatisation des formats ... pour que tes formules s'ajustent en fonction du nombre de lignes relmplies, un essai avec:

Une formule nommée Derlig (par insertion / Nom / définir):

=EQUIV(9^9;'2010'!$A:$A;1)

qui cherche la dernière ligne renseignée avec un date en colonne A

en cellule C2:

=SOMME.SI(INDIRECT("$D$18:$V$"&DerLig);B2;INDIRECT("$E$18:$W$"&DerLig))

même principe pour les cellules d'en dessous

En cellule D16:

=NBVAL(INDIRECT("D18:D"&DerLig))

En D15:

=SOMME(INDIRECT("$E$18:$E$"&DerLig);D14)

@ ++

mth

 

[mth]

Re : Insertion ligne automatique et mise en forme

re

Petits aménagements pour arriver à nos fins sans macros, tu trouveras ton fichier en PJ avec comme dans le post précédant la zone nommée, puis:

en C2 copiée vers le bas:

=SOMME.SI(INDIRECT("$D$18:$V$"&DerLig);B2;INDIRECT("$E$18:$W$"&DerLig))

En D15 copiée vers la droite:

=SOMME(INDIRECT(CAR(65+COLONNE())&"18:"&CAR(65+COLONNE())&DerLig);D14)

En D16 copiée vers la droite:

=NBVAL(INDIRECT(CAR(64+COLONNE())&"18:"&CAR(64+COLONNE())&DerLig))

Ensuite des mises en forme conditionnelles:

de A18 à C150:

=ESTVIDE($A18)=FAUX

couleur jaune

de D18 à W150, une ligne sur deux est en couleur si la date est renseignée en colonne A:

=ET(ESTVIDE($A18)=FAUX;MOD(LIGNE();2)=0)

(Pour simplifier, je n'ai mis qu'une couleur pour toutes les colonnes, je n'ai alterné que les lignes)

Du coup, tu peux mettre tes protections, vois de ton coté si ça fonctionne comme tu le souhaites.

Bonne soirée et à bientôt,

mth
(PS fichier au format 2007)

 

[Captaincake]

Re : Insertion ligne automatique et mise en forme

Re

Et merci pour ton aide ça fonctionne nickel Je n'aurais jamais trouvé tout ça tout seul.

Juste un dernier point, j'aimerais que les colonnes D18: D, F18:F, H18:H, ... soient sous forme de liste déroulante lorsque le tableau "s'étend" automatiquement parce qu'à la longue la saisie risque de devenir fastidieuse pour l'utilisateur.
C'est à dire étendre la validation de données quand la dernière ligne de la colonne A est renseignée. Il faut sûrement passer par une macro je présume.

 

[Captaincake]

Re : Insertion ligne automatique et mise en forme

Re,

Ce n'est pas tout à fait ce que je souhaitais mais j'ai réussi à bloquer les listes déroulantes tant que la colonne A n'est pas renseignée.

j'ai nommé ma liste :
"list" fait référence à :
='2010'!$B$1:$B$7

Et pour chaque colonne contenant une liste : D, F, H, J, ... en validation de donné j'ai appliqué la formule :
=SI(ESTVIDE($A18)=FAUX;List)

Donc ça devrais le faire.

Je te remercie MTH pour ton aide même si j'ai pas tout compris comment t'as trouvé ces formules (mais j'y arriverais un jour)

 

[Captaincake]

Re : Insertion ligne automatique et mise en forme

Bonjour,

Je reviens sur ce message juste pour que quelqu'un puisse m'expliquer la formule en D18 :

=SOMME(INDIRECT(CAR(65+COLONNE())&"21:"&CAR(65+COLONNE())&DerLig);D16)

Ça me serais utile pour pouvoir le refaire au cas où.

Parce que bon j'ai bien essayé de comprendre tout seul mais ce fut une tentative ratée.

Je vous joins le fichier (format 2007).

Merci

 

[012318N]

Re : Insertion ligne automatique et mise en forme

Bonjour,

J'ai la même question, mais avec Excel 2003.

Je n'ai pas d'option "Mettre sous forme de tableau" dans le menu

En insérant une ligne, je souhaite conserver la mise en forme de la ligne sélectionnée, y compris la fusion de cellules.

Merci pour votre réponse