Excel: Gare à l’ajout de lignes dans vos formats conditionnels!

Aujourd’hui, je partage avec vous un truc Excel qui vous sera utile si vous utilisez du format conditionnel dans des fichiers qui peuvent être altérés par des utilisateurs, notamment par l’ajout de lignes. Dans l’exemple ci-bas, Je souhaite que la cellule "Statut" soit hachurée, lorsqu’il y a un changement de client (colonne B). Autrement dit, je veux que l’utilisateur du fichier indique le statut à l’aide d’un menu déroulant, pour chaque client.

Dans un premier temps, voici un petit extrait d’un tel tableau.

Format conditionnel: Solution 1

Si les utilisateurs du fichiers étaient dans l’incapacité d’insérer des lignes, la formule suivante serait très efficace comme format conditionnel ($B5=$B4). Mais dans notre exemple, les utilisateurs du fichier peuvent insérer des lignes. Observons quand même l’effet de cette première solution.

Sur l’image ci-bas, on voit que toutes les données concernent le client 1, donc le deuxième et le troisième statut sont hachurés. À noter que dans ce fichier, les numéros des clients doivent être en ordre croissant.

De même, on voit que lorsque l’on change de numéro de client, la cellule de statut n’est plus hachurée, ce qui indique à l’utilisateur qu’il faut se prononcer sur la valeur de cette cellule.

Même chose sur l’image ci-bas, avec une variation dans les numéros de clients. Donc jusque-là, tout fonctionne comme prévu!

Mais supposons maintenant que vous insérez une ligne au-dessus de la ligne 7. Dans ce cas, tout pourrait paraître normal mais ne pas l’être. En effet, si vous placez votre curseur dans la cellule D8 et regardez la formule du format conditionnel, celle-ci est erronée et tente de rapprocher les valeurs de B8 et de B6., plutôt que de comparer la ligne actuelle avec la ligne précédente.

Ce faisant, dans le contexte ci-bas, on voit bien que le format conditionnel ne fonctionne pas bien. En effet, la cellule D8 devrait être hachurée et elle ne l’est pas.

Format conditionnel: Solution 2

C’est pourquoi il est plutôt recommandé de travailler avec la fonction Indirect() et la fonction Ligne() (ou Row() en anglais), qui fonctionnera à tout coup, même lorsqu’un utilisateur ajoutera des lignes dans le tableau. Dans l’image ci-bas, on voit qu’avec cette formule, l’ajout de ligne n’abîme pas le format conditionnel des cellules hachurées. Bien sûr, cela suppose que vous insérez une ligne "copiée".