Imposer une série par une formule dans la validation de données.

[au.net]

Bonjour à tous,

Pouvez vous m'aider car je n'arrive pas à imposer un format dans une cellule d'Excel 2007 ou 2010
Je souhaiterai mettre la formule en Validation de données personnalisée. (pas en VBA)
Un message apparaitrait si ce format n'est pas respecté:

-la lettre T en majuscule sera toujours le premier caractère,

-un à trois chiffres pourra suivre la lettre T ( de 0 à 999)
mais il pourrait y avoir un - ( moins) devant ces 3 chiffres ,

- le signe + ( plus ) suivra le ou les chiffres,

-un à trois chiffres pourra suivre le sigle +

Ce qui pourrait donner par exemple
T0+12
T24+8
T123+6
T6+128
ou
T-2+8
T-23+6
T-6+128

Il n'y aura aucun espace entre les caractères

Je vous remercie de votre aide car je ne suis pas assez fort pour cela !

 

[mapomme]

Re : [Errata] Imposer une série par une formule dans la validation de données.

Bonjour au.net,

ERRATA : je me disait bien, en relisant ce message que quelque chose clochait dans la symétrie de ma formule.
En fait, j'ai mal recopié la formule de mon fichier test perso dans le message ci-dessous et dans le fichier joint.
La bonne formule et le fichier avec la bonne formule sont dans mon message #4 ICI.
En revanche, le raisonnement reste le même.


je pense avoir trouvé une formule de validation qui doit permettre de valider ou non le contenu des cellules A1:A14.

Hélas ! Cette formule est très longue et manifestement trop grande pour être acceptée comme formule de validation dans la boîte de dialogue du menu "Données / Validation des données".

On est donc passé par une colonne auxiliaire (la colonne B) qui contient cette longue formule. Cette formule renvoie VRAI si le contenu de la cellule A est conforme au format désiré sinon elle renvoie FAUX. Cette colonne a été masquée.

La formule de validation (par formule) des cellules A1:A14 devient simplement =B1

La formule en colonne B à tirer vers le bas est (cette formule est incomplète):

=SIERREUR(OU(ET((EXACT(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;0;"");1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;"");"T+"));ET((0+STXT(A1;2;TROUVE("+";A1)-2))>=0;(0+STXT(A1;2;TROUVE("+";A1)-2))<=999));
ET((EXACT(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;0;"");1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;"");"T-+"));ET((0-STXT(A1;2;TROUVE("+";A1)-2))>=0;(0-STXT(A1;2;TROUVE("+";A1)-2))<=999);ET((0+STXT(A1;TROUVE("+";A1)+1;99))>=0;(0+STXT(A1;TROUVE("+";A1)+1;99))<=999)));FAUX)

En gros la formule :

• retire tous les chiffres du code en A1, et vérifie si le reste est égal exactement à "T+" ou "T-+" (cond10, cond20)

.

• dans le cas 1, extrait de A1 la chaine de caractères entre la position 2 et avant le +, et vérifie que c'est un nombre entre 0 et 999 (cond11)
• dans le cas 1, extrait de A1 la chaine de caractères après la position du + et la fin de A1, et vérifie que c'est un nombre entre 0 et 999 (cond12)

.

• dans le cas 2, extrait de A1 la chaine de caractères entre la position 2 et avant le +, prend l'opposé du nombre, et vérifie que c'est un nombre entre 0 et 999 (cond21)
• dans le cas 2, extrait de A1 la chaine de caractères après la la position du + et la fin de A1, et, vérifie que c'est un nombre entre 0 et 999 (cond22 que est équivalente à la cond12)

.

• si le cas 1 renvoie VRAI ou le cas 2 renvoie VRAI, alors le code en A1 est conforme sinon il n'est pas conforme

.

• enfin il y a le dernier cas où la formule aboutit à une erreur, dans ce cas, le code en A1 n'est pas conforme non plus.

Ce qui donne pour la formule:

 =SIERREUR ( ou( et(cond10;cond11;cond12) ; et(cond20;cond21;cond22))) ; FAUX )

nota : une MFC met le texte en italique rouge si le code de la cellule en colonne A n'est pas conforme.

 

[au.net]

Re : Imposer une série par une formule dans la validation de données.

Ouaaouuh ... Quelle formule, bravo mapomme et merci pour cette résolution que je viens de tester, cela fonctionne à merveille et avec en plus une cerise sur le gâteau le " détail du fonctionnement ", une MFC et message d'erreur.
Félicitations pour ce travail qui va me rendre mon fichier bien plus pratique maintenant en empêchant de mettre des valeurs erronées .

 

[mapomme]

Re : [Correction] Imposer une série par une formule dans la validation de données.

Bonjour au.net, le forum,

Dans mon précédent message, je n'ai pas recopié la bonne formule ni dans le texte du message ni dans le fichier.
Mille excuses à tous .

Voici donc la bonne formule à mettre en B1 et à tirer vers le bas et le bon fichier joint

=SIERREUR(OU(ET((EXACT(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;0;"");1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;"");"T+"));ET((0+STXT(A1;2;TROUVE("+";A1)-2))>=0;(0+STXT(A1;2;TROUVE("+";A1)-2))<=999);ET((0+STXT(A1;TROUVE("+";A1)+1;99))>=0;(0+STXT(A1;TROUVE("+";A1)+1;99))<=999));ET((EXACT(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;0;"");1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;"");"T-+"));ET((0-STXT(A1;2;TROUVE("+";A1)-2))>=0;(0-STXT(A1;2;TROUVE("+";A1)-2))<=999);ET((0+STXT(A1;TROUVE("+";A1)+1;99))>=0;(0+STXT(A1;TROUVE("+";A1)+1;99))<=999)));FAUX)

Le bon fichier: version v2

 

[au.net]

Re : [Correction] Imposer une série par une formule dans la validation de données.

Pas de souci mapomme, merci encore pour cette formule.

Un ébéniste ne fait pas une sculpture dès le premier coup de rabot.