Erreurs #ref lors de l'ajout automatique de lignes

[Jean.delanou]

Bonjour,

Je travaille actuellement sur un fichier EXCEL qui me pose soucis. En effet, Dans une cellule j'ai une formule qui fait référence aux cellules précédentes :

=SIERREUR(SI(ET(B15<B14;B14>B13;B13<B12;B12>B11;B11<B10;B10>B9;B9<B8;B8>B7:B7<B6;B6>B5;B5<B4;B4>B3;B3<B2);"CRITERE 4";SI(ET(B15>B14;B14<B13;B13>B12;B12<B11;B11>B10;B10<B9;B9>B8;B8<B7:B7>B6;B6<B5;B5>B4;B4<B3;B3>B2);"CRITERE 4";"OK"));"ok E")

Vous remarquerez qu'un ajout de la fonction SIERREUR permet "d'ignorer" les erreurs des cellules de début de colonne. Cependant, même lorsque j'ai désactiver toutes les fonctionnalités de verification d'erreur, excel me remplace ma formule par #REF lors de l'ajout automatique de ligne. Lorsque je met précédent, la formule apparait normalement et n'indique qu'une erreur sans tout remplacer par #REF, sachant qu'il n'y a aucuns problèmes... Les cellules dont je fais appelle existent, les valeurs sont cohérentes... Je précise que sur toutes les autres cellules, une erreur est signalée et me propose de "Restaurer en tant que formule de colonne calculée" et si je clique dessus, #REF apparaît.

J'aimerais que l'on m'explique pourquoi Excel fait-il cela et comment le bloquer car l'idée c'est que le classeur se remplisse automatiquement.

Voilà un fichier exemple, un peu exhaustif, mais au moins, il y toutes mes colonnes. Le problème concerne les dernières colonnes avec comme titre "Critère 2", "Critère 3"...



Merci à vous

 

[Lone-wolf]

Bonsoir Jean

En visualisant la 2ème ligne j'ai ceci: =SIERREUR(SI(ET(B2>(C2+D2);B1>(C1+D1);#REF!>(#REF!+#REF!);#REF!>; et est-ce normal que tu aie 2 fois la même formule ?

=SIERREUR(SI(ET(B15<B14;B14>B13;B13<B12;B12>B11;B11<B10;B10>B9;B9<B8;B8>B7:B7<B6;B6>B5;B5<B4;B4>B3;B3<B2);"CRITERE4";

SI(ET(B15>B14;B14<B13;B13>B12;B12<B11;B11>B10;B10<B9;B9>B8;B8<B7:B7>B6;B6<B5;B5>B4;B4<B3;B3>B2);"CRITERE 4";"OK"));"ok E")

Et une erreur ici: B8>B7:B7<B6

 

[Jean.delanou]

Bonjour,
Un grand merci pour votre réponse. Effectivement, au niveau de la ligne 2, les valeurs précédentes n'existent pas, il y a donc une erreur #REF.
Je ne comprends pas votre remarques "et est-ce normal que tu aie 2 fois la même formule ?", à quel niveau j'ai 2 fois la même formule? Au niveau de la colonne I et V?

Effectivement, j'ai une erreur qui s'est glissée, j'ai corrigé. Merci beaucoup.

Mais comment faire pour garder le remplissage automatique des lignes par les formules malgré les "incohérences" du début?

 

[Lone-wolf]

Bonjour Jean

À propos de la remarque

=SIERREUR(SI(ET(B15<B14;B14>B13;B13<B12;B12>B11;B11<B10;B10>B9;B9<B8;B8>B7:B7<B6;B6>B5;B5<B4;B4>B3;B3<B2);"CRITERE4";SI(ET(B15>B14;B14<B13;B13>B12;B12<B11;B11>B10;B10<B9;B9>B8;B8<B7:B7>B6;B6<B5;B5>B4;B4<B3;B3>B2);"CRITERE 4";"OK"));"ok E")

Ici il faut prendre en condition une seule cellule et étirer vers le bas, c'est pour ça que la formule est incohérante. Donc il faut commencer en B3. Je peux étirer la formule avec une macro, mais il faut me montrer la formule corrigée. Un exemple

=SIERREUR(SI(B2=0;"CRITÈRE4";SI(B3>B2;"OK";"okE"));"")

 

[Jean.delanou]

Encore merci de votre réponse. Les formules que vous avez-vu, j'en ai fait une seule :

=SIERREUR(SI(OU(B100>(C100+3*D100);B100<(C100-3*D100));"CRITERE 1";SI(ET(B100>(C100+D100);B99>(C99+D99);B98>(C98+D98);B97>(C97+D97);B96>(C96+D96);B95>(C95+D95);B94>(C94+D94);B93>(C93+D93);B92>(C92+D92));"CRITERE 2";SI(ET(B100<(C100-D100);B99<(C99-D99);B98<(C98-D98);B97<(C97-D97);B96<(C96-D96);B95<(C95-D95);B94<(C94-D94);B93<(C93-D93);B92<(C92-D92));"CRITERE 2";SI(ET(B95<B96;B96<B97;B97<B98;B98<B99;B99<B100);"CRITERE 3";SI(ET(B95>B96;B96>B97;B97>B98;B98>B99;B99>B100);"CRITERE 3";SI(ET(B100<B99;B99>B98;B98<B97;B97>B96;B96<B95;B95>B94;B94<B93;B93>B92;B92<B91;B91>B90;B90<B89;B89>B88;B88<B87);"CRITERE 4";SI(ET(B100>B99;B99<B98;B98>B97;B97<B96;B96>B95;B95<B94;B94>B93;B93<B92;B92>B91;B91<B90;B90>B89;B89<B88;B88>B87);"CRITERE 4";SI(ET(B100>E100;B99>E99;B98>E98);"CRITERE 5";SI(ET(B100>E100;B99<E99;B98>E98);"CRITERE 5";SI(ET(B100>E98;B99>E99;B98<E98);"CRITERE 5";SI(ET(B100<E100;B99>E99;B98>E98);"CRITERE 5";SI(ET(B100<F100;B99<F99;B98<F98);"CRITERE 5";SI(ET(B100<F100;B99>F99;B98<F98);"CRITERE 5";SI(ET(B100<F98;B99<F99;B98>F98);"CRITERE 5";SI(ET(B100>F100;B99<F99;B98<F98);"CRITERE 5";SI(ET(B100>(C100+D100);B99>(C99+D99);B98>(C98+D98);B97>(C97+D97);B96>(C96+D96));"CRITERE 6";SI(ET(B100>(C100+D100);B99>(C99+D99);B98>(C98+D98);B97>(C97+D97);B96<(C96+D96));"CRITERE 6";SI(ET(B100>(C100+D100);B99>(C99+D99);B98>(C98+D98);B97<(C97+D97);B96>(C96+D96));"CRITERE 6";SI(ET(B100>(C100+D100);B99>(C99+D99);B98<(C98+D98);B97>(C97+D97);B96>(C96+D96));"CRITERE 6";SI(ET(B100>(C100+D100);B99<(C99+D99);B98>(C98+D98);B97>(C97+D97);B96>(C96+D96));"CRITERE 6";SI(ET(B100<(C100+D100);B99>(C99+D99);B98>(C98+D98);B97>(C97+D97);B96>(C96+D96));"CRITERE 6";SI(ET(B100<(C100-D100);B99<(C99-D99);B98<(C98-D98);B97<(C97-D97);B96<(C96-D96));"CRITERE 6";SI(ET(B100<(C100-D100);B99<(C99-D99);B98<(C98-D98);B97<(C97-D97);B96>(C96-D96));"CRITERE 6";SI(ET(B100<(C100-D100);B99<(C99-D99);B98<(C98-D98);B97>(C97-D97);B96<(C96-D96));"CRITERE 6";SI(ET(B100<(C100-D100);B99<(C99-D99);B98>(C98-D98);B97<(C97-D97);B96<(C96-D96));"CRITERE 6";SI(ET(B100<(C100-D100);B99>(C99-D99);B98<(C98-D98);B97<(C97-D97);B96<(C96-D96));"CRITERE 6";SI(ET(B100>(C100-D100);B99<(C99-D99);B98<(C98-D98);B97<(C97-D97);B96<(C96-D96));"CRITERE 6";SI(ET(B100<(C100+D100);B100>C100;B99<C99;B99>(C99-D99);B98<(C98+D98);B98>C98;B97<C97;B97>(C97-D97);B96<(C96+D96);B96>C96;B95<C95;B95>(C95-D95);B94<(C94+D94);B94>C94;B93<C93;B93>(C93-D93);B92<(C92+D92);B92>-D92;B91<C91;B91>(C91-D91);B90<(C90+D90);B90>C90;B89<C89;B89>(C89-D89);B88<(C88+D88);B88>C88;B87<C87;B87>(C87-D87);B86<(C86+D86);B86>C86);"CRITERE 7";SI(ET(B100<C100;B100>(C100-D100);B99<(C99+D99);B99>C99;B98<C98;B98>(C98-D98);B97<(C97+D97);B97>C97;B96<C96;B96>(C96-D96);B95<(C95+D95);B95>C95;B94<C94;B94>(C94-D94);B93<(C93+D93);B93>C93;B92<C92;B92>(C92-D92);B91<(C91+D91);B91>C91;B90<C90;B90>(C90-D90);B89<(C89+D89);B89>C89;B88<C88;B88>(C88-D88);B87<(C87+D87);B87>C87;B86<C86;B86>(C86-D86));"CRITERE 7";SI(ET(B100<(C100-D100);B99<(C99-D99);B98<(C98-D98);B97<(C97-D97);B96<(C96-D96);B95<(C95-D95);B94<(C94-D94);B93<(C93-D93));"CRITERE 8";SI(ET(B100>(C100+D100);B99>(C99+D99);B98>(C98+D98);B97>(C97+D97);B96>(C96+D96);B95>(C95+D95);B94>(C94+D94);B93>(C93+D93));"CRITERE 8";"OK")))))))))))))))))))))))))))))));"OK E")

D'après votre exemple, il faut remplacer SIERREUR par lacellule <>"" ?

Merci à vous!

 

[Lone-wolf]

Re

Non. Regarde cet exemple, je ne prend qu'une seule cellule, et ensuite je l'étire vers le bas.

=SIERREUR(SI(B2=0;"CRITÈRE4";SI(B3>B2;"OK";"okE"));"")

 

[Jean.delanou]

Je ne suis pas certain de comprendre car j'ai inséré ma formule dans une seule cellule, j'ai ensuite eu =#REF dans toutes les cellules de ma colonne. J'ai donc fait précédent afin de retrouver ma formule. Je l'ai ensuite étirée.

Pour ce qui est de votre formule, en gros, si B2 renvoie 0, alors cela renvoie critère 4. Mais cela ne correspond pas au critère 4. Le OK E est là pour m'indiquer quand il y a une erreur (La fameuse #REF) en début de colonne et quelque part bypasser celle-ci afin qu'excel continue à me garder l'ajout de ligne automatique.

 

[Lone-wolf]

Re

Jean, la formule est la à titre d'exemple et non à utiliser.

 

[Jean.delanou]

Je voie! Les erreurs que vous avez mentionné, ne sont plus présentes dans cette formule (qui combine toutes les autres) :

=SIERREUR(SI(OU(B100>(C100+3*D100);B100<(C100-3*D100));"CRITERE 1";SI(ET(B100>(C100+D100);B99>(C99+D99);B98>(C98+D98);B97>(C97+D97);B96>(C96+D96);B95>(C95+D95);B94>(C94+D94);B93>(C93+D93);B92>(C92+D92));"CRITERE 2";SI(ET(B100<(C100-D100);B99<(C99-D99);B98<(C98-D98);B97<(C97-D97);B96<(C96-D96);B95<(C95-D95);B94<(C94-D94);B93<(C93-D93);B92<(C92-D92));"CRITERE 2";SI(ET(B95<B96;B96<B97;B97<B98;B98<B99;B99<B100);"CRITERE 3";SI(ET(B95>B96;B96>B97;B97>B98;B98>B99;B99>B100);"CRITERE 3";SI(ET(B100<B99;B99>B98;B98<B97;B97>B96;B96<B95;B95>B94;B94<B93;B93>B92;B92<B91;B91>B90;B90<B89;B89>B88;B88<B87);"CRITERE 4";SI(ET(B100>B99;B99<B98;B98>B97;B97<B96;B96>B95;B95<B94;B94>B93;B93<B92;B92>B91;B91<B90;B90>B89;B89<B88;B88>B87);"CRITERE 4";SI(ET(B100>E100;B99>E99;B98>E98);"CRITERE 5";SI(ET(B100>E100;B99<E99;B98>E98);"CRITERE 5";SI(ET(B100>E98;B99>E99;B98<E98);"CRITERE 5";SI(ET(B100<E100;B99>E99;B98>E98);"CRITERE 5";SI(ET(B100<F100;B99<F99;B98<F98);"CRITERE 5";SI(ET(B100<F100;B99>F99;B98<F98);"CRITERE 5";SI(ET(B100<F98;B99<F99;B98>F98);"CRITERE 5";SI(ET(B100>F100;B99<F99;B98<F98);"CRITERE 5";SI(ET(B100>(C100+D100);B99>(C99+D99);B98>(C98+D98);B97>(C97+D97);B96>(C96+D96));"CRITERE 6";SI(ET(B100>(C100+D100);B99>(C99+D99);B98>(C98+D98);B97>(C97+D97);B96<(C96+D96));"CRITERE 6";SI(ET(B100>(C100+D100);B99>(C99+D99);B98>(C98+D98);B97<(C97+D97);B96>(C96+D96));"CRITERE 6";SI(ET(B100>(C100+D100);B99>(C99+D99);B98<(C98+D98);B97>(C97+D97);B96>(C96+D96));"CRITERE 6";SI(ET(B100>(C100+D100);B99<(C99+D99);B98>(C98+D98);B97>(C97+D97);B96>(C96+D96));"CRITERE 6";SI(ET(B100<(C100+D100);B99>(C99+D99);B98>(C98+D98);B97>(C97+D97);B96>(C96+D96));"CRITERE 6";SI(ET(B100<(C100-D100);B99<(C99-D99);B98<(C98-D98);B97<(C97-D97);B96<(C96-D96));"CRITERE 6";SI(ET(B100<(C100-D100);B99<(C99-D99);B98<(C98-D98);B97<(C97-D97);B96>(C96-D96));"CRITERE 6";SI(ET(B100<(C100-D100);B99<(C99-D99);B98<(C98-D98);B97>(C97-D97);B96<(C96-D96));"CRITERE 6";SI(ET(B100<(C100-D100);B99<(C99-D99);B98>(C98-D98);B97<(C97-D97);B96<(C96-D96));"CRITERE 6";SI(ET(B100<(C100-D100);B99>(C99-D99);B98<(C98-D98);B97<(C97-D97);B96<(C96-D96));"CRITERE 6";SI(ET(B100>(C100-D100);B99<(C99-D99);B98<(C98-D98);B97<(C97-D97);B96<(C96-D96));"CRITERE 6";SI(ET(B100<(C100+D100);B100>C100;B99<C99;B99>(C99-D99);B98<(C98+D98);B98>C98;B97<C97;B97>(C97-D97);B96<(C96+D96);B96>C96;B95<C95;B95>(C95-D95);B94<(C94+D94);B94>C94;B93<C93;B93>(C93-D93);B92<(C92+D92);B92>-D92;B91<C91;B91>(C91-D91);B90<(C90+D90);B90>C90;B89<C89;B89>(C89-D89);B88<(C88+D88);B88>C88;B87<C87;B87>(C87-D87);B86<(C86+D86);B86>C86);"CRITERE 7";SI(ET(B100<C100;B100>(C100-D100);B99<(C99+D99);B99>C99;B98<C98;B98>(C98-D98);B97<(C97+D97);B97>C97;B96<C96;B96>(C96-D96);B95<(C95+D95);B95>C95;B94<C94;B94>(C94-D94);B93<(C93+D93);B93>C93;B92<C92;B92>(C92-D92);B91<(C91+D91);B91>C91;B90<C90;B90>(C90-D90);B89<(C89+D89);B89>C89;B88<C88;B88>(C88-D88);B87<(C87+D87);B87>C87;B86<C86;B86>(C86-D86));"CRITERE 7";SI(ET(B100<(C100-D100);B99<(C99-D99);B98<(C98-D98);B97<(C97-D97);B96<(C96-D96);B95<(C95-D95);B94<(C94-D94);B93<(C93-D93));"CRITERE 8";SI(ET(B100>(C100+D100);B99>(C99+D99);B98>(C98+D98);B97>(C97+D97);B96>(C96+D96);B95>(C95+D95);B94>(C94+D94);B93>(C93+D93));"CRITERE 8";"OK")))))))))))))))))))))))))))))));"OK E")

Or mon problème est toujours là... Lorsque j'insère ma formule, tout se remplace par =#REF, donc je fais un glisser jusqu'en bas etc... Cela à fait un peu évoluer mon problème malgré tout car lors de l'ajout d'une nouvelle ligne, excel ne cherche plus à étirer la formule et m'afficher un = #REF mais ne fait rien. Donc votre idée de macro qui étire automatiquement pourrait être intéressant. Comment procédez-vous?

 

[Jean.delanou]

Mais, si je ne prend qu'une seule cellule, cela modifie mon opération, c'est pour cela que je comprenais pas... Alors, je vais expliquer ces critères :



Je suis obligé de prendre plusieurs cellules... Pardon on s'était mal compris.

 

[Lone-wolf]

Re

J'ai regardé le lien, mais c'est compliqué pour moi. Il faut souhaiter que l'un des pros d'XLD vienne résoudre ton problème.

 

[Jean.delanou]

Je comprends, le lien envoyé est dans mes cordes mais tout retranscrire sur excel... C'est une autre affaire !
Merci en tout cas