Par formule : validation des données (liste) complexe.

[dionys0s]

Bonjour tout le monde

en gros je souhaiterais qu'en fonction de paramètres renseignés, la liste de validation d'une cellule change automatiquement.
Je suis obligé de fonctionner par formule.
J'ai réussi grâce à une formule à pointer sur la bonne plage dans le bon onglet en fonction de la saisie d'une cellule et de paramètres renseignés dans deux autres, mais impossible de la passer en formule pour ma liste de validation. Je sais que ma formule marche puisque si je la DECALE() d'une ligne vers le bas, où j'ai des valeurs renseignées, et que j'applique une somme dessus, la somme renvoyée est correcte. C'est donc que la plage renvoyée par ma fonction l'est non ? Donc je ne comprends pas pourquoi la formule ne veut pas se mettre en liste de validation des données.

Mon fichier exemple en dira plus sur le fonctionnement des paramètres.
Merci d'avance pour votre aide 😎

Good day

dionys0s

 

[chris]

Re : Par formule : validation des données (liste) complexe.

Bonjour

Si j'ai bien compris : avec des plages nommées et la fonction INDIRECT

 

[dionys0s]

Re : Par formule : validation des données (liste) complexe.

Bonjour Chris

merci pour ta suggestion, mais j'ai effectivement oublié de préciser que je suis obligé de me passer de plages nommées. Me demande pas pourquoi, c'est dans le cadre du boulot, et je ne peux pas influer là dessus.

 

[Jocelyn]

Re : Par formule : validation des données (liste) complexe.

Bonjour le Forum,
Bonjour dionysos,

un essai en fichier joint si j'ai bien compris

Cordialement

EDIT : Oup's Bonjour et bises chris, désolé pour la collision

 

[dionys0s]

Re : Par formule : validation des données (liste) complexe.

Re le forum,
bonjour Jocelyn

merci pour ta solution, mais elle ne peux pas s'appliquer. J'ai en effet oublié de préciser qu'il peut y avoir des intitulés de colonnes dans les colonnes précédant et après la zone à renvoyer en liste de validation.
Mais effectivement, le résultat proposé par ta formule directement en liste de validation est le bon, à partir du moment où il n'y a aucun intitulé sur la ligne, mais dès que j'en rajoute, ça ne marche plus (à cause du NBVAL, sur lequel je ne peux pas compter). C'est pour ça que je suis obligé de fonctionner avec les paramètres renseignés en cellules B2 et B3. Je mets le fichier à jour dans mon premier post, et le joins également avec ce post ci.

D'avance merci en tout cas.

 

[chris]

Re : Par formule : validation des données (liste) complexe.

Bonjour Jocelyn

Ces collisions avec toi sont toujours un plaisir🙂

En plus tu donnes la solution sans noms qu'attendait dionys0s... donc la collision est bienvenue pour lui aussi ;-)

Edit : la bonne solution à dionys0s qui ne semble pas l'avoir testée, car sauf si je n'ai rien compris, elle fonctionne parfaitement

 

[Jocelyn]

Re : Par formule : validation des données (liste) complexe.

re,

Plaisir partagé chris 🙂,

@dionysos effectivement le nbval() ne peut pas convenir vu que tu a d'autre intitulés alors un essai en remplaçant le nbval par un sommeprod regardes et dis nous

 

[dionys0s]

Re : Par formule : validation des données (liste) complexe.

Re,

ça me semble plus que pas mal ! Il faut juste espérer que personne ne s'amuse à mettre un intitulé de colonne identique à un indicateur et c'est parfait ! Et j'ai remplacé le 2 de GAUCHE(*;2) par NBCAR de la cellule du dessus et c'est parfait.
Super aide ! merci beaucoup !

Une idée cependant de pourquoi ma formule qui était correcte pour faire une somme ne passait pas en liste de validation ?

 

[chris]

Re : Par formule : validation des données (liste) complexe.

Bonjour

Bizarre car chez moi, malgré le NBVAL de la formule initiale de Jocelyn, la liste s'affiche correctement (si libellés avant seulement) : je suis sous 2010. Pas chez vous ?

Edit : sur 2003 je suis à peu près sûre que les mentions vides s'affichent mais quid de 2011 ?

Edit 2 : on peut aussi remplacer le NBVAL par NB.SI

NB.SI(INDIRECT("'DONNEES "&$E$20&"'!1:1");E20&"*")

 

[dionys0s]

Re : Par formule : validation des données (liste) complexe.

Re,

tu veux dire que avec le NBVAL et des intitulés autour des colonnes contenant les indicateurs, la validation fonctionne correctement ??? Ce serait pour le moins étrange je crois.

Pour 2011, il affiche les mentions vides.

 

[chris]

Re : Par formule : validation des données (liste) complexe.

Bonjour

J'ai vérifié : sur 2013 et 2010 si la définition de la liste inclus des vides au début ou à la fin, la liste déroulante ne les affiche pas.

C'est nouveau mais je l'ignorais car je n'ai pas vu d'info le signalant.

Par contre si tu as des mentions après cela ne marcherait pas.

Donc SOMMEPROD ou NB.SI ...

 

[dionys0s]

Re : Par formule : validation des données (liste) complexe.

Re tout le monde

en fait je me suis planté. La solution par SOMMEPROD ne marche pas puisque j'aurai des intitulés d'indicateurs qui ne commenceront pas par le nom de la grille... Du coup je suis (j'ai l'impression) obligé de fonctionner avec mes deux cellules qui renseignent respectivement la première et la dernière colonne d'indicateur...

J'ai essayé de créer une fonction VBA que j'utiliserais dans la formule pour la liste de validation, mais quelle que soit la méthode que j'ai choisie (une version dans laquelle la fonction renvoie un Range, une autre dans laquelle elle concatène les valeurs trouvées en les séparant par un ";"), l'utilisation de ma fonction personnalisée dans l'assistant de liste de validation m'est interdite... Je commence à réellement douter de la faisabilité de mon truc, mais si vous pensez à autre chose n'hésitez pas à me dire.

D'avance merci 🙂

 

[Jocelyn]

Re : Par formule : validation des données (liste) complexe.

re,

Si j'ai bien compris par exemple pour la grille "DONNEES AA" tous les indicateurs ne commenceront pas par AA alors comment feras tu pour trouver la premier colonne des indicateur si le premier n'est pas AA même chose pour le dernier ?

 

[dionys0s]

Re : Par formule : validation des données (liste) complexe.

Re,

c'est exactement ça. Les rangs de la première et de la dernière colonne concernée (ces colonnes là étant forcément contiguës) sont renseignées en cellule B2 et B3 dans mon classeur exemple. Donc en récupérant l'index de la grille sélectionnée, on peut isoler les paramètres nécessaires.

INDIRECT("'DONNEES "&PARAMS!$E$20&"'!L1C"&STXT(PARAMS!$B$2;SIERREUR(CHERCHE("µ";SUBSTITUE(PARAMS!$B$2;",";"µ";EQUIV(PARAMS!$E$20;PARAMS!$H$2:$H$6;0)-1);1)+1;1);SIERREUR(CHERCHE("µ";SUBSTITUE(PARAMS!$B$2;",";"µ";EQUIV(PARAMS!$E$20;PARAMS!$H$2:$H$6;0));1)-SIERREUR(CHERCHE("µ";SUBSTITUE(PARAMS!$B$2;",";"µ";EQUIV(PARAMS!$E$20;PARAMS!$H$2:$H$6;0)-1);1)+1;1);NBCAR(PARAMS!$B$2)))&":L1C"&STXT(PARAMS!$B$3;SIERREUR(CHERCHE("µ";SUBSTITUE(PARAMS!$B$3;",";"µ";EQUIV(PARAMS!$E$20;PARAMS!$H$2:$H$6;0)-1);1)+1;1);SIERREUR(CHERCHE("µ";SUBSTITUE(PARAMS!$B$3;",";"µ";EQUIV(PARAMS!$E$20;PARAMS!$H$2:$H$6;0));1)-SIERREUR(CHERCHE("µ";SUBSTITUE(PARAMS!$B$3;",";"µ";EQUIV(PARAMS!$E$20;PARAMS!$H$2:$H$6;0)-1);1)+1;1);NBCAR(PARAMS!$B$3)));FAUX)

Avec cette fonction, j'arrive à pointer vers la bonne plage (si je la décale sur une plage où j'ai des valeurs, et que je somme la plage, j'obtiens les bons résultats), mais impossible de l'utiliser en validation des données. Comprends pas...

(EDIT : cette fonction pas belle du tout et pas digeste non plus est détaillée étape par étape, et utilisée en cellule E22 dans mon classeur exemple)

 

[Dranreb]

Re : Par formule : validation des données (liste) complexe.

Bonjour.
Réussi avec une fonction rendant un Range :

Function LstVal() As Range
Set LstVal = Feuil6.[E1:I1]
End Function

Mais même pas essayé de le faire accepter à la liste de validation. À la place accepté =FLstVal
Et FlstVal: un nom dans le classeur avec pour référence =LstVal()
Mais je ne suis pas convaincu de la pertinence de ce moyen.