SOLVED

L'insertion d'une ligne dans une table ne duplique pas les formules

Copper Contributor

Bonjour,

Pour mieux gérer les données de mon tableur, j'ai transformé le contenu en table.

J'avais compris que lorsque l'on insère une ligne dans un tableau, les formules et les MEFC étaient reconduites pour chaque colonnes, ce que je voulais.

Or dans mon cas, cela ne marche pas, toutes les cellules de la nouvelle ligne insérée sont vides.

Qu'ai-je oublié ? Merci à vous par avance.

3 Replies
best response confirmed by Pat-J (Copper Contributor)
Solution
Hello there,

This is most likely an issue with the Table not recognizing the formula as a Calculated Column. This is something Excel senses when entering formulas, although there is an internal audit to determine the data type for a column. Before I tell you how to force this, there is the possibility of a setting being off, which you should check.

File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type:
Two settings reside here, ensure they're both checked. They are:
• Include new rows and columns in table
• Fill formulas in tables to create calculated columns

The latter of the above two items is the one you need checked for this specific issue. However, the former item should also be checked when working with Tables.

To ensure a Table column is recognized as a Calculated Column:
Copy the formula in desired column. Any row.
Select the entire column. Do not include headers or total row.
Clear contents. The keyboard 'Delete' key will do this, or right-click > clear contents.
Paste the formula into a single cell in desired column, confirm with ENTER.

The above should work for you. Ensure you are not filtering the Table or have any hidden rows when you do this.

@Zack Barresse 

Thanks a lot !! Merci beaucoup, cela marche après avoir effacé les formules des cellules et copié ces formules dans les colonnes sans l'entête.

Et ce qui est bien aussi c'est que les mises en forme conditionnelles des colonnes restent aussi inchangées lorsque l'on insère une ligne. 

Cela va bien me simplifier la tache !  Merci encore

Patrick

Je suis content que ça ait marché pour toi! (Google translate ftw)
1 best response

Accepted Solutions
best response confirmed by Pat-J (Copper Contributor)
Solution
Hello there,

This is most likely an issue with the Table not recognizing the formula as a Calculated Column. This is something Excel senses when entering formulas, although there is an internal audit to determine the data type for a column. Before I tell you how to force this, there is the possibility of a setting being off, which you should check.

File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type:
Two settings reside here, ensure they're both checked. They are:
• Include new rows and columns in table
• Fill formulas in tables to create calculated columns

The latter of the above two items is the one you need checked for this specific issue. However, the former item should also be checked when working with Tables.

To ensure a Table column is recognized as a Calculated Column:
Copy the formula in desired column. Any row.
Select the entire column. Do not include headers or total row.
Clear contents. The keyboard 'Delete' key will do this, or right-click > clear contents.
Paste the formula into a single cell in desired column, confirm with ENTER.

The above should work for you. Ensure you are not filtering the Table or have any hidden rows when you do this.

View solution in original post