Excel - dependent drop down list?

[Freakster86]

Ik werk zelden met excel, maar ben toch een poging aan het doen om mijn eigen (soort van) zorgvergelijker te maken. Nu loop ik tegen iets praktisch aan. Ik probeer een drop down lijst afhankelijk te maken van de waarde in een cel, zodat die dropdownlijst niet oneindig lang wordt, maar enkel de relevante dingen toont.


A2 = drop down lijst met "namen" die komen uit tabel1

In de cel B2 wil ik het volgende:
B2 = drop down lijst die bestaat uit het resultaat van het volgende:

• de naam van A1 wordt gezocht in tabel2, kolom1(zie hieronder)
• bij een match (veelal meerdere, zie ook plaatje hieronder) komt de cel in die rij in kolom2 (dus tabel2, kolom2) in het drop down lijstje te staan.

Dus als in het eerste plaatje A2 = "CZ"
Dan B2 in het eerste plaatje = drop down lijst met enkel "CZ Zorgbewust; CZ Zorg-op-maat; CZ Zorgkeuze"

Ik heb al de halve middag zitten googlen en vond o.a. links met dependent drop down list, vlookup, match, index forumules, maar dit lijkt niet hetgeen te doen wat ik wil. Veelal wordt er gewerkt met aparte lijstjes waarna dependent drop down lijsten worden gemaakt, maar ik kom nergens hetgeen tegen dat ik probeer te bereiken (werkend vanuit een tabel met twee kolommen, zoeken en matchen in de eerste kolom en vervolgens als resultaat de bijbehorende cel uit de tweede kolom). Iemand die de gouden tip heeft?

[Joep]

Filter toepassen?

[Freakster86]

@Joep En hoe moet ik dat dan precies toepassen in dit geval?

Ik heb (voor nu) maar even de naam van de basisverzekeringen aangepast met de naam van de verzekeraar ervoor (dus =A2&" "&B2 op het tweede plaatje toegepast en de uitkomst in kolom B gezet), zodat het wat duidelijker scrollen is door de drop down lijst.

Ik heb nog doorgezocht, maar kan enkel dingen vinden waarbij het in principe gaat om het transponeren van de Verzekeraars en alle relevante basis (of aanvullende verzekeringen) daaronder te zetten en dan met een INDIRECT of iets dergelijks dependent drop down lijsten te maken. Dat lijkt de enige manier te zijn tot dusver, maar niet echt ideaal. Zeker niet met 324 aanvullende verzekeringen...

In tussen alvast een stapje verder zitten denken en gekeken naar dependent cascading drop down, omdat het ook wel fijn zou zijn dit toe te kunnen passen op de aanvullende verzekeringen om te kunnen combineren.


Dit is wat ik het liefst zie

• In B2(basis) afhankelijk is van waarde in A2(verzekeraar)
• In F2(aanvullend) afhankelijk is van de waarde in A2(verzekeraar)

En nog een stapje verder

• In H2(aanvullend2) afhankelijk is van de waarde in A2(verzekeraar) - de waarde in F2(aanvullend)

etc.


Iets anders nog. Ik loop tegen het max aantal tekens in een formule aan. Totaalprijs bestaat nu hier uit, maar kan dus eigenlijk geen vergoedingen meer toepassen. Enige manier die ik kan bedenken is de berekening te splitsen in extra velden en de uitkomst op te tellen in Totaalprijs? Of bevat de volgende regel misschien overbodige formules dat iemand zo kan zien (te veel gevraagd misschien..)? Begin zelf daarin ook het overzicht te verliezen. Het werkt wel en daar gaat het om haha.

code:

=ALS(P2=0;"0,00";ALS(P2>0;SOM(P2*12)+MAX(Situaties!$C$4-U2;0)+MAX(Situaties!$S$4-AD2;0)+ALS(AA2<100;Situaties!$E$4-(Situaties!$E$4*(AA2/100)))+ALS(AB2<100;Situaties!$F$4-(Situaties!$F$4*(AB2/100)))+ALS(V2<100;(Situaties!$L$4-(Situaties!$L$4*(V2/100))))+ALS(Y2<100;(Situaties!$O$4-(Situaties!$O$4*(Y2/100))))-S2+ALS(AJ2<0;(ABS(AJ2)))+ALS(AK2<0;(ABS(AK2)))+ALS(AL2<0;(ABS(AL2)))))

[Maurice__Smit]

Ik werk vaak met offset functies hier, let op! Dit is (net als Indirect) een Volatiele functie en derhalve maakt het je workbook trager.

Anyways, met de offset functie kun je uit de lijst met 'typen verzekeringen' een subrange selecteren (door een 'startrij' en een 'aantal rijen' mee te geven). Je 'startrij' is daarbij een 'match' op je verzekering (bv waar begint CZ -- functie Match, en de lengte is het aantal keren dat CZ voorkomt -- functie Countif).
https://www.contextures.com/xlDataVal13.html

Enkele vereiste, je verzekeringen lijst moet gesorteerd zijn op verzekeraar. Dus de CZtjes staan bij elkaar. Voordeel van deze methode is dat je door kolommen samen te voegen (concatenate) je ook meerdere niveaus diep kunt gaan (dus een derde, vierde afhankelijke drop down).

Een andere optie is door simpelweg alle verzekeraars op een rij te zetten (naast elkaar) en de verzekering types eronder. Dan kun je met dezelfde offset de juiste kolom erbij zoeken met opties. Nadeel van deze optie is dat je ergens dus je data moet herstructureren.

[Freakster86]

Maurice__Smit schreef op woensdag 26 december 2018 @ 01:31:
Een andere optie is door simpelweg alle verzekeraars op een rij te zetten (naast elkaar) en de verzekering types eronder. Dan kun je met dezelfde offset de juiste kolom erbij zoeken met opties. Nadeel van deze optie is dat je ergens dus je data moet herstructureren.

Ja, hier liep ik steeds tegenaan. En dat lijkt mij niet handig, althans ik weet niet hoe ik (los van handmatig) de namen van elke aanvullende verzekering onder de juiste kolom/verzekeraar krijg.

Maurice__Smit schreef op woensdag 26 december 2018 @ 01:31:
Anyways, met de offset functie kun je uit de lijst met 'typen verzekeringen' een subrange selecteren (door een 'startrij' en een 'aantal rijen' mee te geven). Je 'startrij' is daarbij een 'match' op je verzekering (bv waar begint CZ -- functie Match, en de lengte is het aantal keren dat CZ voorkomt -- functie Countif).
https://www.contextures.com/xlDataVal13.html

Enkele vereiste, je verzekeringen lijst moet gesorteerd zijn op verzekeraar. Dus de CZtjes staan bij elkaar. Voordeel van deze methode is dat je door kolommen samen te voegen (concatenate) je ook meerdere niveaus diep kunt gaan (dus een derde, vierde afhankelijke drop down).

Bedankt! Ik had zelf ook nog doorgezocht en het volgende gevonden en dat is volgens mij wat jij omschrijft, toch?
YouTube: Excel: Extract unique items for dynamic data validation drop down list
YouTube: Excel: Find Multiple Matches & Dependent Drop Down List (Unsorted Ta...

De eerste heb ik net al gevolgd en toegepast op de aanvullende pakketten (kolom met verzekeraar) met dit als resultaat:

code:

Kolom1
=ALS.FOUT(INDEX(Totaal12[Verzekeraar];VERGELIJKEN(0;INDEX(AANTAL.ALS($Q$1:Q1;Totaal12[Verzekeraar]););0));"")
en vervolgens als Unique drop down list
=VERSCHUIVING($Q$2;;;AANTAL.ALS($Q$2:$Q$39;"?*"))

Dit lijkt goed te werken. Nu met video twee koppelen aan de aanvullende pakketten en opnieuw doen voor de tabel met de basisverzekeringen. Maar moet dan nog zien uit te vinden hoe ik allebei aan één drop down kan koppelen, dus wanneer bijvoorbeeld "Anderzorg" wordt geselecteerd, dat zowel de UDDL van basis én aanvullend enkel de opties achter "Anderzorg" laten zien. En daarna natuurlijk nog de aanvullende opties laten "cascaden". Maar goed, het is meer fine tunen, want het geheel lijkt al goed te werken (oranje kolommen moeten nog), maar het is nu veel gemakkelijker om verschillende situaties te vergelijken.


EDIT:

Gelukt! Drop down lijsten onder Basis en Aanvullend zijn nu afhankelijk van Verzekeraar Dat youtube kanaal is echt een goede vondst. Maakt het allemaal weer iets gemakkelijker.