Verbetering gegevensvalidatie met INDIRECT

[LJA]

Goededag,
in bijgevoegd excel-bestand heb ik op het tabblad "Planning" in de geel gemarkeerde kolommen B en C met gegevensvalidatie en de formule =INDIRECT twee afhankelijke treklijstjes ingesteld.
In kolom B dient een "Team" gekozen te worden, waarna in kolom C een collega uit dat team gekozen dient te worden.
Ik wil de gegevensvalidatie zo instellen, dat in de kolommen B en C alleen waardes uit de lijsten / kolommen op het tabblad "Colleagues" gekozen kunnen worden en dat er geen andere namen ingevuld kunnen worden.

Door een spatie in de lijst "Team" op te nemen en deze vooraf in alle cellen in de kolom B in te vullen, kunnen er geen andere waardes ingevuld worden in de kolommen B en C. (Als je dat probeert, verschijnt er een 'stop'-waarschuwing.)

Wanneer de spatie in kolom B echter verwijderd wordt, kan in kolom C wel weer een andere, ongewenste waarde ingevuld worden.
Daarom ben ik op zoek naar een oplossing, die ook zonder die 'workaround' met de spatie werkt.
Kan en wil iemand me hierbij helpen?
Bij voorbaat hartelijk dank.

 

[emields]

bedoel je zoiets?

 

[LJA]

@emields, dank voor de snelle reactie, maar dit beidt geen oplossing.
In je bestand is het nog steeds mogelijk om op het tabblad "Planning" (na het verwijderen van de spatie in de cel in kolom B) in de cel in kolom C een willekeurige waarde in te voeren.

(Wat je op het gegevens-tabblad "Colleagues" heb gedaan, is me niet duidelijk.)
De simpele tabel op het tabblad "Colleagues" dient puur om de op het tabblad "Planning" te kiezen waardes te leveren. Hier hoeft naar mijn idee, de oplossing niet gezocht te worden.

Ik denk dat het mogelijk moet zijn om met een bepaalde formule in de gegevensvalidatie in de cellen in kolom C (op tabblad "Planning") het doel te bereiken. Ik weet alleen nog niet hoe. Mijn kennis en ervaring schiet hier nog tekort.

Bij de gegevensvalidatie in de cellen van kolom B staat: hier mag alleen een waarde uit de lijst "Team" worden ingevuld. Dat werkt.
Bij de gegevensvalidatie in de cellen van kolom C moet iets staan in de trant van: hier mag alleen een vervolgwaarde uit de lijst van het gekozen "Team" worden ingevuld. Dat werkt nu alleen als er in kolom B een team is gekozen. Als er in kolom B nog geen team is gekozen, mag er nog niets ingevuld worden in kolom C.

Ik heb geprobeerd in cel C2 van kolom C de gegevensvalidatie in te stellen als:
Toestaan: Aangepast
Formule: =ALS(B2="";"";=INDIRECT(B2))
maar dit wordt niet geaccepteerd en ik krijg de melding: er is een prbleem gevonde met de formule.

Om ook in de cel C2 een treklijst te behouden, moet ik hier volgens mij kiezen voor:
Toestaan: Lijst
maar hier kan ik dan geen formule meer invullen... (of (met een omweg) wel?)

Hopelijk kan iemand me verder helpen.

 

[popipipo]

Ik begrijp niet goed waarom je nu precies die spatie er hebt ingezet?
Volgens mij werkt het bestand prima zonder die spatie.

 

[emields]

Als je het gebruikt zoals ik het geplaatst heb voldoet dat aan al uw verwachtingen. Zoek anders maar eens op getrapte validatie. Of plaats een relevant voorbeeld.

 

[LJA]

@popipipo: ik heb in de lijst "Team" een op het oog 'leeg' team met een spatie aangemaakt en dit 'team' (de spatie) in de cellen van Kolom B op het tabblad "Planning" geselecteerd. Op die manier is er al iets onzichtbaars ingevuld in de cellen van kolom B, waardoor er niet iets willekeurigs in de cellen van kolom C op het tabblad "Planning" ingevuld kan worden.
Verwijder je de spatie in een cel van kolom B, dan er in de naast gelegen cel in kolom C wel iets willekeurigs ingevuld worden. En juist dit (iets willekeurigs invullen) wil ik in de cellen van kolom B en C van tabblad "Planning" verkomen.
Probeer het maar eens met en zonder de spatie in kolom B.

@emields: Zoals geprobeerd en aangegeven kan ik in je bestand nog steeds op het tabblad "Planning" (na het verwijderen van de spatie in de cel in kolom B) in de cel in kolom C een willekeurige waarde in te voeren.
Dit is wat ik wil voorkomen.
Kun je toelichten, waarom je het tabblad "Colleagues" omgebouwd hebt? Wat heb je hiermee willen bereiken? Dit is me namelijk niet duidelijk.

 

[LJA]

@emields: in jouw bestand ziet het tabblad "Colleagues" er bij mij nu zo uit, als ik hem open.
Was dat je bedoeling?


De treklijstjes op het tabblad "Planning" werken nu niet meer zoals bedoeld:

 

[emields]

Je kan zelfs geen spatie plaatsen in kolom B. Ik vraag me af hoe je daar toe komt in het doorgestuurde bestand?

 

[Warme bakkertje]

Ik denk niet dat wat jij wil bereiken mogelijk is zonder gebruik te maken van VBA.

Ik heb echter een alternatief met VW die de C-kolom zwart kleurt zolang er geen waarde is ingevuld in de B-kolom. Van zodra een waarde geselecteerd word in de B-kolom verdwijnt de zwarte kleur.
Je kan uiteraard nog wel waardes invullen in de C-kolom bij een lege B-kolom maar ze zijn dan niet zichtbaar. Het is enkel een visuele waarschuwing dat er iets niet juist is.
Probeer het eens uit en laat maar weten of dit een eventuele oplossing is.

 

[LJA]

Goededag,

allen die een bijdrage hebben geleverd: hartelijk dank daarvoor!

Ik heb zelf nog wat verder gepuzzeld en het (zonder VBA) werkend gekregen, zoals ik het voor ogen had.
In het bijgevoegde, versimpelde bestand is het te proberen.
Mijn vraag: ziet iemand nog mogelijkheden om het geheel / de gebruikte formules te versimpelen? Ik doel dan met name op de stappen 4 en 5 hieronder.

DOELSTELLING
Mijn doelstelling was om een gebruiker in de cellen in de kolommen B en C van het tabblad "Planning" alleen vooraf ingestelde waardes te laten kiezen (of invullen), die ik op het tabblad "Colleagues" heb opgenomen.
Wanneer iemand iets anders probeert in te vullen in de cellen in de kolommen B en C van het tabblad "Planning", verschijnt er een foutmelding.

STAPPEN
Ik heb de volgende stappen gezet om het excel-bestand naar mijn wens werkend te krijgen.
1. Op het tabblad "Colleagues" is een tabel opgenomen met collega's, die verdeeld zijn in teams.
2. De volgende namen zijn aangemaakt en worden beheerd:

3. Op het tabblad "Planning" is in de cellen van kolom B een treklijst gemaakt met de volgende gegevensvalidatie : Toestaan: Lijst, Bron: =Team
4. Op het tabblad "Spill" worden dmv de volgende formule in de cellen in kolom A de collega's uit het op tabblad "Planning" in de cellen van kolom B gekozen team "gespilld":
=TRANSPONEREN(ALS(ISLEEG(INDIRECT(Planning!B2));"";INDIRECT(Planning!B2)))
(Hier zouden de 'lege' cellen ook daadwerkelijk leeg moeten zijn, maar in de treklijst in de cellen van kolom C op tabblad "Planning" levert dit toch ongewenst een 'lege' positie op onder de namen.
Daarom heb ik ook de volgende stap gezet.)
5. Op het tabblad "Spill" worden dmv de volgende formule in de cellen van kolom K de 'lege cellen', die in de 'spill' van stap 4 overblijven verwijderd en ontstaat vanaf kolom K een 'schone' spill-lijst met alleen namen:
=FILTER(A2:I2;A2:I2<>"")
6. Op het tabblad "Planning" is in de cellen van de kolommen C een treklijst gemaakt met de volgende gegevensvalidatie : Toestaan: Lijst, Bron: =Spill!$K2#

In het eind-document bescherm en verberg ik de tabbladen "Colleagues" en "Spill".
Ook het tabblad "Planning" wordt dan op bepaalde punten beschermd.
Het mooie van deze opzet is dat een gebruiker op het tabblad "Planning" straks nog rijen kan toevoegen en de treklijsten dan in alle cellen van de kolommen B en C nog naar wens blijven werken.

Ik ben benieuwd of iemand nog mogelijkheden ziet om het geheel te verbeteren / versimpelen, terwijl de al bereikte doelstelling intact blijft!

 

[emields]

Ik dacht dat het de bedoeling was van een persoon geen 2 keer te kunnen kiezen? De enige mogelijkheid daarvoor staat in #2