Microsoft 365 Liste déroulante dynamique

Ananda

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

Pièces jointes

  • liste IP - Copie.xlsx
    20.5 KB · Affichages: 8
Solution
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 :
VB:
=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 :
Code:
=SIERREUR(CNUM(SUBSTITUE(H3;".";""));"")
3- On trie ces valeurs, avec pour conséquence de supprimer les "trous" :
Code:
=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:
Code:
=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...

JHA

XLDnaute Barbatruc
Bonjour à tous,

Avec ce que je comprends mais sans liste déroulante
VB:
=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
 

Pièces jointes

  • liste IP - Copie.xlsx
    22.2 KB · Affichages: 2

Ananda

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

XLDnaute Barbatruc
Supporter XLD
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 :
VB:
=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 :
Code:
=SIERREUR(CNUM(SUBSTITUE(H3;".";""));"")
3- On trie ces valeurs, avec pour conséquence de supprimer les "trous" :
Code:
=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:
Code:
=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.:)
 

Pièces jointes

  • liste IP - Copie.xlsx
    30.7 KB · Affichages: 3

sylvanu

XLDnaute Barbatruc
Supporter XLD
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 :
VB:
=INDEX(H3:H1000;EQUIV(VRAI;INDEX((H3:H1000<>"");0);0))
qui donne la première IP disponible.
 

Pièces jointes

  • liste IP - Copie V2.xlsx
    24.2 KB · Affichages: 5

Ananda

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

Ananda

XLDnaute Nouveau
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:
Code:
=INDEX(I3:I1000;EQUIV(VRAI;INDEX((I3:I1000<>"");0);0))
par la formule:
Code:
=INDEX(List;PETITE.VALEUR(SI(List<>"";LIGNE(INDIRECT("1:"&LIGNES(List))));LIGNES($1:1)))
ce qui permet d'avoir toute les IP "filtrées"....
 

Pièces jointes

  • liste IP - Copie V2 modifié.xlsx
    27.3 KB · Affichages: 8

sylvanu

XLDnaute Barbatruc
Supporter XLD
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 :
VB:
=SIERREUR(INDEX(List3;PETITE.VALEUR(SI(List3<>"";LIGNE(INDIRECT("1:"&LIGNES(List3))));LIGNES($1:1)));"")
Toujours en matriciel.
 

Discussions similaires