Discussion :

[joel.drigo]

Bonjour,

Je génère automatiquement un classeur Excel (en Java, avec Apache POI) dans lequel je génère automatiquement et de manière générique des colonnes de données. Pour chacune d'elles, je génère un nom qui représente la liste des valeurs de la colonne de données (sans la première ligne, qui contient une entête).
Ailleurs (autre feuille ou autre colonne), je voudrais faire une validation de saisie, avec liste déroulante, telle que la liste de choix proposés dépende de la valeur saisie dans la cellule située immédiatement à sa gauche (enfin peu importe, une valeur saisie dans une autre cellule disons). Dans cette cellule, on peut saisir un choix également dans une liste, elle statique. Mettons pour l'exemple qu'il y a dans cette liste les valeurs suivante "v1","v2","v3","v4".
La liste de choix à proposer, mettons que je l'ai nommé "listedechoix", ne doit être affiché que pour le choix "v2" et le choix "v3".

J'ai tenté de résoudre ça en créant deux noms, val_v2 et val_v3 comme étant "=listedechoix". Puis j'ai défini une règle de validation avec la formule =indirect("val_"&$j$1;false). Cela ne fonctionne pas : j'ai un message d'erreur laconique qui me dit "Impossible de trouver une plage nommée que vous avez spécifiée". Avec la formule =listedechoix ça fonctionne, avec =val_v2, ça fonctionne et avec =indirect("listedechoix";false), ça fonctionne aussi. En revanche, =indirect("val_v2";false) ne fonctionne pas.

Bien sûr, c'est un exemple simplifié (j'ai réduit le nombre de la valeurs possibles, de listes, etc). En plus, je pourrais avoir plusieurs listes de choix différentes (pas seulement une liste ou pas de liste selon la valeur (comme par exemple, "v1":aucune valeur, "v2":listedechoix,"v3":listedechoix, "v4":listeautreschoix)). Je voudrais également conserver la généricité de mon code, en rendant indépendant la colonne qui contient les valeurs de celle qui a besoin de la validation (d'où le passage par 2 noms, en particulier).

1. J'ai l'impression que c'est la référence "en cascade" à un nom qui n'est pas gérée. Me trompe-je ?
2. Y'a-t-il une erreur dans la formule =indirect("val_"&$j$1;false). Question connexe : y-a-t-il un moyen de connaître quelle est la plage nommée déterminée qu'Excel ne trouve pas...
3. Y'a-t-il un (autre) moyen, une fonction (pour faire un indirect sur le résultat d'un indirect ), une syntaxe pour le faire (sans macro, ni vba, ni autre du genre) ? J'ai essaye par evalue sans succès : =evalue("=indirect(""val_"&$j$1""";false)"). Le problème étant toujours que je ferais référence à une plage nommée introuvable...si je pouvais au moins connaître le nom généré, ça m'aiderai peut-être).
4. Y'aurait-t-il moyen de faire une sorte de switch ou if/elseif sans passer par du vba pour pouvoir choisir une liste de choix ou une autre en fonction de la saisie, ce qui me permettrait de choisir une liste différente selon la valeur saisie dans l'autre cellule ?
5. Suis-je condamné à passer par un seul nom, ce qui m'obligerait à créer autant de listes que de variantes de liste et de mettre ces noms en valeur dans le cellules d'une feuille bidon pour pouvoir y faire référence en indirect (ce que je cherche à tout prix à éviter (la feuille bidon)) ?

Merci d'avance pour vos suggestions.

[mat955]

Bonsoir Joel.drigo

Si j'étais toi, je ne générerai qu'un seul et unique nom, qui lui ferai référence à une plage dynamique en fonction de ta fameuse cellule connexe.
Plus besoin de s'arracher les cheveux avec une fonction INDIRECT dans la validation de saisie puisque tu fais références toujours au même nom.
En revanche, une belle fonction DECALER (il faudra peut-être en imbriquer plusieurs) te sera nécessaire pour affecter la bonne colonne/plage à ton nom.

Est-ce clair ? Besoin de plus d'infos ?
Ou peut-être ai-je mal compris ?

A+, mat.

[joel.drigo]

Merci de ta réponse.
Je ne suis pas sûr de bien comprendre : tu parles de définir la validation comme étant une fonction qui intègre directement la référence de colonne de référence, et les colonnes sources, c'est ça ? Genre ce que je pourrais faire avec un if($D$1="machin", $b$1:$b$xxx, if ($D$2="truc", $e$1:$e$xxx)... je l'avais envisage, mais la limite de taille de la définition est bien trop courte (je galère déjà avec les listes en dur que j'envisage d'abandonner au profit de listes en dur mises dans une feuilles cachées)

En revanche, je ne comprends pas bien comment je pourrais utiliser cette fonction "décaler" : les colonnes sources ne sont ni contiguës, ni sur la même feuille (sur plusieurs feuilles différentes d'ailleurs), ni dans un ordre cohérent par rapport aux valeurs de la colonne référence. Je crains de devoir faire une formule ultra-complexe, non ? D'autant plus que je n'ai pas précisé que l'utilisateur devra pouvoir insérer des colonnes à volontée à certains endroits (ce qui va, j'ai l'impression, complexifier le calcul de tout décalage, parce qu'il me faudra retrouver par rechercheh l'index de colonne à référencer pour déterminer un décalage).

Pour être plus concrêt, voilà un schéma simplifié de ce que je cherche à réaliser :

Mettons feuille sources de données (générée mais complétable, modifiable par l'utilisateur):

Nom	Prop1	Prop2	Prop3
X	...	...	...
Y	...	...	...
T	...	...	...

La première colonne dans cet exemple définit des noms qui consituent les valeurs que je dois pouvoir saisir. J'ai plusieurs feuilles avec ce genre de noms. Sur certaines feuilles, j'ai plusieurs colonnes utilisables. Donc plusieurs plages différentes. Dans certains cas, j'ai plusieurs versions de plages sur la même colonne (en gros, certaines valeurs constituent un cas, certaines autres un autre), ou alors toutes les valeurs ne peuvent pas être utilisées (filtre d'exclusion, en fonction de valeurs de propx). Pour l'instant, j'essaye de résoudre le cas simple : la plage qui est définie comme la colonne Nom. Le reste, je ne sais même pas encore si c'est possible (genre générer un nom avec un filtre du type la liste des valeurs de Nom telles que Prop2=machin et/ou Prop3=truc) .

Dans la saisie (idem générée, mais modifiable par l'utilisateur), j'ai :

(Autre)nom	Type (saisi par l'utilisateur, à partir d'une liste contrainte en dur, dont je connais les valeurs d'avance)	Colonne de Saisie	Autre...
A	Type 1	là par exemple j'aurais la plage Nom X Y Z T
B	Type 2	là par exemple j'aurais une liste vide (ou pas de liste de préférence)
C	Type 3	là par exemple j'aurais une autre plage AutreNom, de la même feuille que Nom, ou d'une autre feuille, voire de la même feuille dans laquelle je suis en train de saisir, comme par exemple ma colonne 1 (Autre)Nom A B C D E
D	Type 4	Là par exemple j'aurais une liste partielle de valeurs de la colonne Nom Z T
E	Type 5	Et là autre exemple j'aurais une liste en dur (ne correspondant pas à une saisie utilisateur) YES NO

Pour l'instant, je m'en suis sorti avec du recherchev : j'ai une table dans une feuille cachée (very hidden) qui me sert de map valeur/nom de plage. Je ne voulais pas faire ça au début parce que je ne voulais pas exposer ce genre de table mais je ne connaissais pas cette possibilité (very hidden) que j'ai découvert aujourd'hui.

L'étape suivante serait de pouvoir faire des cellules de types différents (avec une validation de type différent) en fonction de la valeur saisie, dans une troisième colonne (voire d'autres, avec d'autres règles encore), mais là, j'ai vraiment l'impression que c'est impossible avec Excel, sans faire du VBA.

Une autre chose que je n'ai pas précisé : j'utilise dans tous les cas la validation pour l'instant, mais pour certain cas (les plus complexes en particulier), le but surtout est de fournir une aide à la saisie, pas forcément une contrainte, donc je pourrais éventuellement passer par un autre mécanisme s'il en existe un autre.