Validatie dropdown.

[jverkerk]

Ik zit altijd te klieren met het maken van de juiste dropdown gegevens op de juiste manier.
Kan iemand mij daar even de juiste richting wijzen of een formule voor de tweede validatie.

 

[AHulpje]

In cel D16:

=VERT.ZOEKEN(B16;Tabel2[[Tolknaam]:[Taal1]];2;ONWAAR)

Validatieregel voor cel D16:

=INDIRECT("B"& VERGELIJKEN(B16;Tolknaam;0)+2&":E"&VERGELIJKEN(B16;Tolknaam;0)+2)

 

[jverkerk]

Bedankt AHulpje,
Is het mogelijk om de validatieregel dynamisch te maken dat als er een taal bij komt hij die vanzelf meeneemt.
Of wat zou ik dan moeten doen?

 

[AHulpje]

Validatieformule wordt dan een tikkeltje langer:

=INDIRECT("B"& VERGELIJKEN(B16;Tolknaam;0)+2&":"&SUBSTITUEREN(ADRES(1;6;4);1;"")&VERGELIJKEN(B16;Tolknaam;0)+2)

 

[jverkerk]

Hij geeft met de nieuwe validatieregel tot Taal 5 kolom F.

 

[AHulpje]

SUBSTITUEREN(ADRES(1;6;4);1;"") maakt van kolomnummer 6 een F.
Kolommen(Tabel2) levert het aantal kolommen in Tabel2, als je de tabel uitbreidt met Taal 5 levert Kolommen(Tabel2) het getal 6 op.
Dus SUBSTITUEREN(ADRES(1;Kolommen(Tabel2);4);1;"") geeft dan ook de letter F.
Als ik vervolgens de validatieformule

=INDIRECT("B"& VERGELIJKEN(B16;Tolknaam;0)+2&":"&SUBSTITUEREN(ADRES(1;[COLOR=#ff0000]6[/COLOR];4);1;"")&VERGELIJKEN(B16;Tolknaam;0)+2)

wijzig in

=INDIRECT("B"& VERGELIJKEN(B16;Tolknaam;0)+2&":"&SUBSTITUEREN(ADRES(1;[COLOR=#ff0000]Kolommen(Tabel2)[/COLOR];4);1;"");4);1;"")&VERGELIJKEN(B16;Tolknaam;0)+2)

dan geeft Excel niet thuis.
Als je die formule plakt in een cel geeft hij wél netjes alle talen weer.

Workaround:
Plaats deze formule in een cel naar keuze, bijvoorbeeld in cel C16:

=SUBSTITUEREN(ADRES(1;KOLOMMEN(Tabel2);4);1;"")

Refereer in de validatieformule naar die cel:

=INDIRECT("B" & VERGELIJKEN(B16;Tolknaam;0)+2 & ":" & C16 & VERGELIJKEN(B16;Tolknaam;0)+2)

Dan gaat het wel goed, vraag me niet waarom.
Iemand?

 

[jverkerk]

Hartelijk bedankt AHulpje,
Ik vind het weer een hele uitvinding, het werkt perfect met die hulpcel.

 

[HSV]

Geen hulpcel.

In namen beheren deze formule een naam geven; bv. talen.

=VERSCHUIVING(GEGEVENS!$B$2;VERGELIJKEN(GEGEVENS!$B$16;Tolknaam;0);;;AANTALARG(VERSCHUIVING(GEGEVENS!$B$2;VERGELIJKEN(GEGEVENS!$B$16;Tolknaam;0);;;KOLOMMEN(Tabel2)-1)))

In de validatie: =talen

@AHulpje.
In deze formule,....

=INDIRECT("B"& VERGELIJKEN(B16;Tolknaam;0)+2&":"&SUBSTITUEREN(ADRES(1;Kolommen(Tabel2);4)[COLOR=#ff0000];1;"");4)[/COLOR];1;"")&VERGELIJKEN(B16;Tolknaam;0)+2)

......van je zit een foutje.

aangepast naar.

=INDIRECT("B"& VERGELIJKEN(B16;Tolknaam;0)+2&":"&SUBSTITUEREN(ADRES(1;KOLOMMEN(Tabel2);4);1;"")&VERGELIJKEN(B16;Tolknaam;0)+2)

Zet het in namen beheren en je hebt geen hulpcel nodig.

Enige verschil dat die van mij geen lege velden heeft in de validatielijst.

 

[HSV]

Je kan ook nog ergens een hulpformule plaatsen; bv. A100.

Passend in Excel 2019

=TEKST.SPLITSEN(TEKST.COMBINEREN("\";1;FILTER(VERSCHUIVING(Tabel2;;1);Tolknaam=B16));"\")

In de validatie verwijs je dan naar die cel met daarachter een hashtag. =A100#

In Office 365 kon het met:

=NAAR.RIJ(FILTER(VERSCHUIVING(Tabel2;;1;;KOLOMMEN(Tabel2)-1);Tolknaam=B16);1)

 

[jverkerk]

Bedankt Harry voor de betere oplossing, alleen kan ik het er niet inkrijgen of ik doe iets fout.
Zal het bestandje erbij doen misschien kun je het voor mij compleet maken.
Kan ik meteen zien wat ik fout doe.
De tabelnaam is wel veranderd en ik heb geprobeerd het aan te passen maar helaas.

 

[AHulpje]

Oeps

Ik heb de formule van Harry er in gezet, die is net even wat mooier dan die van mij.
Ook even de VERT.ZOEKEN functie gebruikt om bij het wisselen van tolknaam geen "foute" taal te zien.

Oeps, die VERT.ZOEKEN wordt natuurlijk onmiddellijk overschreven bij keuze van een andere taal!

 

[HSV]

Een formule in een cel met een validatie zetten heeft geen zin.
Eenmaal een keuze gemaakt en de formule is verdwenen.

 

[jverkerk]

Volgens mij doet hij nu wat hij moet doen.

 

[jverkerk]

AHulpje of Harry,
Zou je hem in bijgaand bestand kunnen zetten, er gaat bij mij iets fout omdat ik waarschijnlijk de formule niet goed kan ontleden, ik heb de gevoelige gegevens verwijderd.

 

[AHulpje]

Voeg de naam Talen toe en laat die naam verwijzen naar

=VERSCHUIVING(GEGEVENS!$B$2;VERGELIJKEN(DATABASE!$B3;Tolken;0)-1;;;AANTALARG(VERSCHUIVING(GEGEVENS!$B$2;VERGELIJKEN(DATABASE!$B3;Tolken;0)-1;;;KOLOMMEN(tbl.Tolken)-1)))

Vervolgens validatie op kolom G met Lijst =Talen

 

[jverkerk]

AHulpje,
Weer hartelijk bedankt voor de oplossing.
Hopelijk zijn ze er nu blij mee.

 

[jverkerk]

Stel dat ik wel office 365 of 2021 zou hebben ziet de formule er dan anders of makkelijker uit.
Ik had zelf dit gevonden.

=X.VERGELIJKEN(DATABASE!$B3;Tolken;0;0)+1

Alleen het werkt niet.
Nieuwsgierigheid!

 

[snb]

Andere aanpak:

Selecteer per tolk de talen in werkblad 'gegevens', geef dit gebied de naam van de tolk.

De datavalidatie in het werkblad wordt dan eenvoudig: '=indirect($A3)"

Zie de bijlage, waar dat voor Naam1, Naam9 en Naam17 gedaan is.

 

[jverkerk]

Bedankt voor de input @snb.
Het is voor mij ook een bestandje om de formules te snappen.
Het probleem met deze formule is dat het dan niet dynamisch is, ik ga er vanuit dat de verschillende mensen die het moet gebruiken dat niet snappen en er gewoon een naam met een taal of talen bij zetten.

 

[snb]

Bekijk de zaak nog eens op je gemak.

Wat is erop tegen het gebied 'Naam1' zo te definiëren?

=offset(Gegevens!$B$2;0;0;1;counta(gegevens!$B$2:$Z2))