Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Erreurs #ref lors de l'ajout automatique de lignes

J

Jean.delanou

XLDnaute Nouveau
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 :

Code: 
=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
 
Trier par date Trier par votes

Lone-wolf

XLDnaute Barbatruc
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
 
Dernière édition:
Vote positif 0 Vote négatif
J

Jean.delanou

XLDnaute Nouveau
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?
 
Vote positif 0 Vote négatif

Lone-wolf

XLDnaute Barbatruc
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"));"")
 
Dernière édition:
Vote positif 0 Vote négatif
J

Jean.delanou

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

Code: 
=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!
 
Vote positif 0 Vote négatif
J

Jean.delanou

XLDnaute Nouveau
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.
 
Vote positif 0 Vote négatif
J

Jean.delanou

XLDnaute Nouveau
Je voie! Les erreurs que vous avez mentionné, ne sont plus présentes dans cette formule (qui combine toutes les autres) :
Code: 
=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?
 
Vote positif 0 Vote négatif
J

Jean.delanou

XLDnaute Nouveau
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.
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

P
  • Résolu(e)
XL 2021 commission sur vente pour Excel version française
Réponses
6
Affichages
141
Plans24
P
E
  • Résolu(e)
XL 2016 Automatiser calculs d'une feuille synthèse
Réponses
4
Affichages
475
ELOTJ
E
M
  • Question
XL 2010 Récupération de données et les transformer avec Power Query ?
Réponses
3
Affichages
524
DJunqueira
D
  • Résolu(e)
XL 2019 macro conditon
2
Réponses
25
Affichages
1 K
job75
  • Résolu(e)
XL 2019 simplifier un code
2
Réponses
26
Affichages
2 K
Dranreb
A
  • Question
Microsoft 365 combobox deux colonnes avec lignes horizontales
Réponses
8
Affichages
692
AIGOIN
A
  • Question
Microsoft 365 Curiosité : enregistrement impossible à cause de la liste des erreurs (#NUL!, #DIV/0!, #REF!, etc.)
Réponses
1
Affichages
355
Simply
N
  • Résolu(e)
XL 2016 Eviter d'écrire ceci B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17&" "&B18&" "&B19&" "&B20&" "&B21&" "&B22&"
Réponses
5
Affichages
921
nounou1902
N
A
  • Question
XL 2016 Verrouiller automatiquement la colonne précédente après avoir modifier une cellule de la colonne suivante
Réponses
0
Affichages
412
arnoldbrice19
A
M
  • Question
Microsoft 365 interdire l'ajout et la suppression de lignes et de colonnes en vba
Réponses
9
Affichages
390
Eric C
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…