negeren lege cellen in drop down menu

[frankh1]

Ik heb een drop down menu in excel 2007. (zie bijlage)

Het drop down menu staat in cel G8 en de waarden zijn geselecteerd uit kolom B. Ik heb de optie lege cellen negeren aangevinkt, maar dan nog komen deze in mijn drop down menu te staan.
Hoe kan ik ervoor zorgen dat de lege cellen uit kolom b worden genegeerd en alleen de "daadwerkelijke opties" worden weergegeven.

 

[Gotty]

Ik heb een drop down menu in excel 2007. (zie bijlage)Bekijk bijlage 68271

Het drop down menu staat in cel G8 en de waarden zijn geselecteerd uit kolom B. Ik heb de optie lege cellen negeren aangevinkt, maar dan nog komen deze in mijn drop down menu te staan.
Hoe kan ik ervoor zorgen dat de lege cellen uit kolom b worden genegeerd en alleen de "daadwerkelijke opties" worden weergegeven.

1. Tijdens de invoer van de validatie van de cel aangeven of lege cellen moeten worden genegeerd. Op deze wijze heeft de getoonde lijst een overzichtelijke vorm met daarin alleen gevulde elementen. Dit kan in het dialoogscherm bij het invullen van gegevens over validatie door middel van een keuzevak worden aangevinkt.

1. Het is echter ook op een andere (ambachtelijke manier) te realiseren dat lege waarden niet in de lijst met mogelijke waarden worden getoond. Dit kan worden gerealiseerd met de formule "VERSCHUIVING()". Deze werkwijze is als volgt: Het vervolg staat in de bron.

Bron


Mogelijkheid 1 lukt bij mij ook niet

, en van mogelijkheid 2 snap ik niets. Maar als het jouw met mogelijkheid 2 lukt hoor ik dat graag.

 

[HSV]

Hier een mogelijkheid.

 

[Gotty]

Hier een mogelijkheid.

Kun je uitleggen wat de 2 rode streepjes doen?

=ALS(RIJ()-1>SOM(--($B$2:$B$25<>""));"";(INDEX(B$2:B$25;KLEINSTE(ALS($B$2:$B$25<>"";RIJ(B$2:B$25)-1);RIJ(B2)))))
.............................^

 

[HSV]

Kun je uitleggen wat de 2 rode streepjes doen?

=ALS(RIJ()-1>SOM(--($B$2:$B$25<>""));"";(INDEX(B$2:B$25;KLEINSTE(ALS($B$2:$B$25<>"";RIJ(B$2:B$25)-1);RIJ(B2)))))
.............................^

Beste Gotty,

Zet de cursor eens op de formule in de formulebalk en klik op ƒx.
Doorloop de formule eens in de formulebalk en haal de -- eens weg.
De 1/1/0/0/1 die er stonden, veranderen in WAAR/WAAR/ONWAAR/ONWAAR/ enz.

De 1’en zijn nodig om te tellen in de formule =SOM(

Hoop het zo een beetje duidelijk te hebben gemaakt.

Vriendelijke gr.

 

[frankh1]

sorry, maar ik kom er nog niet uit...

 

[HSV]

sorry, maar ik kom er nog niet uit...

Ik heb eigenlijk meer aan waar je niet uitkomt.
Maar volgens mijn vrouw moet ik zo afsluiten.
(zwemmen denk ik)

Verneem het wel van je

 

[Gotty]

Beste Gotty,

Zet de cursor eens op de formule in de formulebalk en klik op ƒx.
Doorloop de formule eens in de formulebalk en haal de -- eens weg.
De 1/1/0/0/1 die er stonden, veranderen in WAAR/WAAR/ONWAAR/ONWAAR/ enz.

De 1’en zijn nodig om te tellen in de formule =SOM(

Hoop het zo een beetje duidelijk te hebben gemaakt.

Vriendelijke gr.

Nou... Niet echt, ik heb gedaan wat je zei maar een formule op deze manier beredeneren is mij te pittig. Als ik die streepjes verwijder verdwijnt de betreffende letter uit de validatielijstje. Wat ik wel heb gedaan is de formule evalueren maar het getal 1 komt niet één keer in beeld. Ik evalueer als in dit tekstbestandje, de eerste regel is de formule van jou.

Ik moet er eerlijk bij zeggen dat als ik aan het eind van de evaluatie ben niet meer weet wat er aan het begin is gebeurd, maar ik vind het wel even interessant om te weten waar die streepjes voor zijn.

Ik dacht zojuist nog even: Als ik het nou wat makkelijker maak voor mijzelf door

=SOM(--($B$2:$B$25<>""))

ergens neer te zetten, dat geeft wél de waarde 1. Maar het wordt mij er niet duidelijker op. Ik hoop op je hulp.

 

[HSV]

Ik hoop op je hulp.

Vanzelfsprekend, wie A zegt,.....
Linker afbeelding met de twee min-tekens.
Rechter zonder.

Zie verschil in evaluatie.

 

[Gotty]

Vanzelfsprekend, wie A zegt,.....
Linker afbeelding met de twee min-tekens.
Rechter zonder.

Zie verschil in evaluatie.

Prachtig HSV, ik heb er wat tekst onder gezet en bewaar het op mijn PC en deel hem ook graag even voor de zoekfunctie-gebruiker: Plaatje. Dus ik ga dan gelijk door prutsen en kijken wat ik verder kan met die streepjes. En als ik nu de formule =som(-(10+5)) maak dan krijg ik -15 als uitkomst, wat eigenlijk wel logisch is bedacht ik me later. Maar kun jij nog andere voorbeelden bedenken waarbij ik zulke streepjes goed kan gebruiken?

In ieder geval heel hartelijk bedankt hiérvoor :thumb:.

 

[bandito bob]

Zoals reeds gezegd maakt -- van een WAAR of ONWAAR een 1 of 0. Dit kan je ook bekomen door de WAAR/ONWAAR op te nemen in een vermedigvuldiging/deling....
Dan zal er vanzelf een 1 of 0 van gemaakt worden. Kijk b.v. naar de vele voorbeelden van de SOMPRODUCT formules.

Met in A1 "ja" en in A2 "ja":
geeft: =A1=A2 >> WAAR
geeft: =--(A1=A2) >> 1
geeft: =(A1=A2)*1 >> 1

Dus: als er na de vergelijking een vermedigvuldiging/deling...volgt wordt er vanzelf een 0 of 1 van gemaakt.

 

[Gotty]

Zoals reeds gesegd maakt -- van een WAAR of ONWAAR een 1 of 0. Dit kan je ook bekomen door de WAAR/ONWAAR op te nemen in een vermedigvuldiging/deling....
Dan zal er vanzelf een 1 of 0 van gemaakt worden. Kijk b.v. naar de vele voorbeelden van de SOMPRODUCT formules.

Met in A1 "ja" en in A2 "ja":
geeft: =A1=A2 >> WAAR
geeft: =--(A1=A2) >> 1
geeft: =(A1=A2)*1 >> 1

Dus: als er na de vergelijking een vermedigvuldiging/deling...volgt wordt er vanzelf een 0 of 1 van gemaakt.

Inderdaad, ik zie het. Heel erg interessant, dankjewel.

 

[frankh1]

hoe kan ik dit gebruiken in mijn probleem met de lege opties in het drop down menu?

 

[HSV]

hoe kan ik dit gebruiken in mijn probleem met de lege opties in het drop down menu?

Frank,

Zoals in het bestandje van topic #3.
Loop de formule in kolom P eens na, en kijk ook naar de validatieformule van cel F8 bij Menu-Data>Valideren.
Anders plaats jezelf je eigen bestandje hier zonder gevoelige info.

 

[frankh1]

Ìk heb de formule bekeken.
=ALS(RIJ()-1>SOM(--($B$2:$B$25<>""));"";(INDEX(B$2:B$25;KLEINSTE(ALS($B$2:$B$25<>"";RIJ(B$2:B$25)-1);RIJ(B15)))))

Maar moet eerlijk bekennen dat ik geen idee heb wat er nou eigenlijk staat.
In het bestandje wordt een aparte validatielijst gemaakt, maar ik had gehoopt het drop down menu direct op kolom B te kunnen baseren.

Is de bovenstaande formule een formule voor het drop down menu of voor het maken van de validatielijst?

 

[HSV]

Ìk heb de formule bekeken.
=ALS(RIJ()-1>SOM(--($B$2:$B$25<>""));"";(INDEX(B$2:B$25;KLEINSTE(ALS($B$2:$B$25<>"";RIJ(B$2:B$25)-1);RIJ(B15)))))

Maar moet eerlijk bekennen dat ik geen idee heb wat er nou eigenlijk staat.
In het bestandje wordt een aparte validatielijst gemaakt, maar ik had gehoopt het drop down menu direct op kolom B te kunnen baseren.

Is de bovenstaande formule een formule voor het drop down menu of voor het maken van de validatielijst?

De formule in kolom P laat de lege rijen weg van kolom B.
De validatielijst is weer gebaseerd op kolom P.
Zie mijn vorig schrijven waar deze formule staat.

 

[Gotty]

Hier een mogelijkheid.

Ik heb de verwijzing van het validatie eens voor de gein geëvalueerd en kom tot deze evaluatie. Hoewel hij natuurlijk prima werkt mag de functie =verschuiving() geen negatief getal getal bevatten voor de hoogte zegt de Excel help:

...
Syntaxis

VERSCHUIVING(verw;rijen;kolommen;hoogte;breedte)
...
hoogte is de hoogte, uitgedrukt in een aantal rijen, die u wilt toekennen aan de resulterende verwijzing. hoogte moet een positief getal zijn.
...

Maar dat bevat hij wel, volgens mijn redenatie althans. En als ik =VERSCHUIVING(P1;1;0;-14;1) opgeef als validatieverwijzing krijg ik een foutmelding. Kun je dit verklaren?

 

[Gotty]

maar ik had gehoopt het drop down menu direct op kolom B te kunnen baseren.

Je kunt kolom P verbergen, dan komt het op hetzelfde neer. Op de kolomletter gaan staan => rechtermuisknop => Verbergen .

 

[Roncancio]

=VERSCHUIVING(P1;1;0;[B]-14[/B];1)

De -14 is het probleem.
Je verschuiving begint bij P1, vervolgens 1 regel omlaag, 0 kolommen naar rechts maar dan 14 regels omhoog!
Dat gaat niet als je op regel 2 zit.
Bovendien kan Excel maar 1 waarde aan in een cel en geen 14.
Daarvoor moet je een datavalidatie gebruiken.

Met vriendelijke groet,


Roncancio

 

[HSV]

Ik heb de verwijzing van het validatie eens voor de gein geëvalueerd en kom tot deze evaluatie. Hoewel hij natuurlijk prima werkt mag de functie =verschuiving() geen negatief getal getal bevatten voor de hoogte zegt de Excel help:



Maar dat bevat hij wel, volgens mijn redenatie althans. En als ik =VERSCHUIVING(P1;1;0;-14;1) opgeef als validatieverwijzing krijg ik een foutmelding. Kun je dit verklaren?

=VERSCHUIVING(P1;1;0;AANTAL.ALS(P225;"*")-AANTAL.ALS($P$2:$P$25;"");1)
=VERSCHUIVING(P1;1;0;AANTAL.ALS(P225;"*")-14;1)
=VERSCHUIVING(P1;1;0;24-14;1)
=VERSCHUIVING(P1;1;0;10;1)

AANTAL.ALS(P225;"*") telt ook de formules.

Hoop dat het zo een beetje duidelijker is Gotty.