Microsoft 365 Liste déroulante dynamique

[Ananda]

Bonjour,

Je souhaite créer une liste déroulante dynamique (et tout ça sans macro) et j’ai un peu de mal à voir comment attaquer le problème.
En effet, j’ai dans onglet 1 (=équipement) une liste de PC colonne B et d’ OS en colonne C auxquels je dois appliquer une IP fixe.

Les IP à appliquer dépendent de l’OS des PC

En onglet 2 (=IP), j’ai les « range » des IP possible en fonction des OS.

Mon souhait serait d’arrivé à créer une liste déroulante (dans l’onglet : Equipement colonne D) pour proposer les IP possible en fonction des OS mais aussi et surtout les IP qui ne sont pas déjà utilisées.

A savoir que dans l’onglet « Equipement », le tableau et surtout la colonne IP n’est pas toujours trié dans l’ordre croissant de IP.

J’ai un peu de mal à voir dans quel sens attaquer le problème.

Si vous avez des idées, je vous remercie d’avance.

 

[JHA]

Bonjour à tous,

Avec ce que je comprends mais sans liste déroulante

=SI(B2<>"";SIERREUR(DECALER(IP!$A$2;NB.SI(C$2:C2;C2);EQUIV(Equipement!C2;IP!$A$1:$D$1;0)-1);"A définir");"")

JHA

 

[Ananda]

Bonjour,

Merci JHA.
J'ai regardé rapidement ta solution mais ta formule ne me convient pas.
J'ai peut être mal exprimé mon besoin.

Dans l'onglet 2 (IP): ce sont toutes les IP possible.
Dans l'onglet 1 (Equipement) ce sont les PC en services avec les IP déjà attribuées. Cette liste est vivante c'est à dire que je peux ajouter et/ou supprimer de PC et donc attribuer une nouvelle IP que je pioche dans l'onglet IP.
Deux PC ne peuvent pas avoir la même IP.
Quand je supprimer un PC, son IP est de nouveau utilisable pour un autre...

Ce que je souhaite faire, c'est une liste déroulante qui va piocher une IP dans l'onglet "IP" mais il faut qu'elle tienne compte des IP déjà utilisées dans l'onglet "Equipement" de manière à ce que la liste ne me propose que des IP non utilisées dans l'onglet "Equipement"

L'onglet "IP" est l'ensemble des possibles et l'onglet "Equipement" est la contrainte.

 

[sylvanu]

Bonjour Amanda, JHA,
En PJ une tentative usine à gaz, le VBA rendrait les choses plus simples.
Dans la feuille IP :
1- On repère les IP non utilisées :

=SI(B4="";"";SI(NB.SI.ENS(Equipement!$C:$C;H$2;Equipement!$D:$D;B4)>0;"";B4))

2- On supprime les points des IP pour avoir des nombres :

=SIERREUR(CNUM(SUBSTITUE(H3;".";""));"")

3- On trie ces valeurs, avec pour conséquence de supprimer les "trous" :

=SI(SIERREUR(GRANDE.VALEUR(K:K;$N3);"")<>"";INDEX(H:H;EQUIV(SIERREUR(GRANDE.VALEUR(K:K;$N3);"");K:K;0));"")

Et on met en liste déroulante en Equipement colonne D:

=SI($C2="Windows 10";Liste10;SI($C2="Windows 7 SP1 ou +";Liste7;ListeXP))

Les listes ont du blanc à la fin, mais supprimer ce blanc rendrait la formule de liste trop longue pour moi.🙂

 

[sylvanu]

Ou peut être moins usine à gaz si l'attribution des IP peut être arbitraire, sans choix possible de l'IP par l'utilisateur.
Dans ce cas l'IP disponible peut être :

=INDEX(H3:H1000;EQUIV(VRAI;INDEX((H3:H1000<>"");0);0))

qui donne la première IP disponible.

 

[Ananda]

Merci beaucoup Sylvanu,

La solution marche parfaitement et fait ce que je souhaite.
Effectivement, je ne pensais pas que cela soit une telle usine à gaz mais la solution est astucieux.
Je vais décortiquer en profondeur ta méthode histoire de mourir moins bête.
Et quand même merci à JHA pour ta participation

 

[sylvanu]

Regardez la seconde proposition, elle est plus simple si vous acceptez une attribution arbitraire de l'IP.

 

[Ananda]

Et merci pour ta dernière réponse.
Je vais regarder ça après le repas de midi mais je pense que ta première solution a ma préférence.
Mais pour ma culture, je vais quand même regarder la solution deux.

 

[Ananda]

Encore merci sylvanu,

Finalement je vais utiliser votre solution N°2. Mais j'ai procédé à une petite modification afin d'avoir toute les IP disponible.
En effet, j'ai remplacé la en J, K, L la formule:

=INDEX(I3:I1000;EQUIV(VRAI;INDEX((I3:I1000<>"");0);0))

par la formule:

=INDEX(List;PETITE.VALEUR(SI(List<>"";LIGNE(INDIRECT("1:"&LIGNES(List))));LIGNES($1:1)))

ce qui permet d'avoir toute les IP "filtrées"....

 

[sylvanu]

Impec.
Mais je n'aime pas les formules matricielles quand on peut les éviter. Ca ralentit le système.
Et vous avez des #Nombre "inesthétiques", on peut les supprimer avec :

=SIERREUR(INDEX(List3;PETITE.VALEUR(SI(List3<>"";LIGNE(INDIRECT("1:"&LIGNES(List3))));LIGNES($1:1)));"")

Toujours en matriciel.

 

[Ananda]

Oui, je comprends pour les formules matricielles.
Mais bon dans mon cas, je ne travaillerai jamais plus qu'avec 200 lignes.

En tout cas merci pour la petite correction de correction d'erreur de votre formule. 🙂