Tableau Formule automatique nouvelle ligne

%3CLINGO-SUB%20id%3D%22lingo-sub-2660132%22%20slang%3D%22fr-FR%22%3ENew%20Row%20Auto%20Formula%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2660132%22%20slang%3D%22fr-FR%22%3E%3CP%3EGood%20morning%3C%2FP%3E%3CP%3EI%20have%20a%20little%20problem%3A%20I%20created%20a%20table%20(created%20with%20Excel%202007%20but%20I%20edit%20it%20on%202016)%20with%20a%20column%20that%20must%20contain%20the%20value%20of%20a%20formula%20based%20on%20ROW().%20The%20formula%20works%20very%20well%3B%20however%2C%20if%20I%20add%20a%20line%20IN%20THE%20MIDDLE%20of%20the%20beach%2C%20the%20new%20line%20is%20blank%2C%20the%20formula%20is%20not%20retrieved%20(no%20formula%20by%20the%20way).%20My%20formula%20compensates%20for%20the%20blank%20line%20correctly%20(e.g.%20301%26gt%3Bline%20blank%26gt%3B303)%2C%20but%20it%20is%20absolutely%20necessary%20that%20the%20new%20line%20includes%20this%20formula.%20I%20prefer%20to%20avoid%20the%20VBA%20as%20much%20as%20possible%2C%20what%20I%20do%20not%20understand%20is%20that%20I%20created%20a%20table%2C%20it%20is%20supposed%20to%20resume%20the%20formulas%20automatically%3F%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2660132%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2660192%22%20slang%3D%22en-US%22%3ERe%3A%20Tableau%20Formule%20automatique%20nouvelle%20ligne%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2660192%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1131180%22%20target%3D%22_blank%22%3E%40Perceval%3C%2FA%3E%26nbsp%3BDid%20you%20create%20a%20structured%20table%3F%20Click%20anywhere%20inside%20your%20table.%20Do%20you%20than%20get%20a%20new%20ribbon%20at%20the%20top%20called%20%22Table%20Design%22%20(don't%20know%20what%20this%20is%20called%20in%20French%20Excel).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20not%2C%20you%20are%20not%20working%20with%20a%20structured%20Excel%20table.%20On%20the%20Home%20ribbon%20you'll%20find%20an%20icon%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-08-18%20at%2013.42.32.png%22%20style%3D%22width%3A%2085px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F304023iCE8E104C1F4D8B44%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-08-18%20at%2013.42.32.png%22%20alt%3D%22Screenshot%202021-08-18%20at%2013.42.32.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAlternatively%2C%20select%20any%20cell%20inside%20the%20data%20range%20and%20press%20Ctrl-T.%20Confirm%20whether%20the%20range%20has%20headers%20or%20not.%20In%20a%20structured%20table%2C%20all%20cells%20with%20a%20formula%20will%20replicate%20when%20you%20insert%20or%20add%20a%20new%20row%20at%20the%20bottom.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Bonjour,

J'ai un petit souci : j'ai créé un tableau (créé avec Excel 2007 mais je l'édite sur du 2016) avec une colonne qui doit contenir la valeur d'une formule basée sur LIGNE(). La formule fonctionne très bien ; or, si je rajoute une ligne AU MILIEU de la plage, la nouvelle ligne est vierge, la formule n'est pas récupérée (aucune formule d'ailleurs). Ma formule compense la ligne vierge correctement (ex. 301>ligne vierge>303), mais il faut absolument que la nouvelle ligne comprenne cette formule. Je préfère éviter au maximum le VBA, ce que je ne comprends pas c'est que j'ai bien créé un tableau, c'est censé reprendre les formules automatiquement ?

Merci !

2 Replies

@Perceval Did you create a structured table? Click anywhere inside your table. Do you than get a new ribbon at the top called "Table Design" (don't know what this is called in French Excel).

 

If not, you are not working with a structured Excel table. On the Home ribbon you'll find an icon like this:

Screenshot 2021-08-18 at 13.42.32.png

Alternatively, select any cell inside the data range and press Ctrl-T. Confirm whether the range has headers or not. In a structured table, all cells with a formula will replicate when you insert or add a new row at the bottom.

@Riny_van_Eekelen Hi ! Thanks for help ; it is indeed a structured table, that's why I can't understand why my formulas are not replicated. I ended up building a new table on another sheet and it works perfectly. I can't understand why, but nothing worked, but it did the trick flawlessly by creating a new table. Lost some hours on it, nevermind, now it works. 

Thanks !