Excel naam bepalen zonder lege waarden

[slimjim]

hallo allemaal

ik ben bezig met een een excel bestandje en ik vroeg mij het volgende af:

ik wil graag een pulldownmenu/gegevensvalidatie;lijst uit een bereik van cellen zonder daarin de lege waarden op te nemen.

voorbeeld:
"optie1"
" "
"optie2"
"optie3"
" "
"optie4"

en dat ik dan een lijst krijg van optie 1 t/m 4 zonder lege mogelijkheden ertussen.

het moelijke hieraan is dat ik graag wil dat dit automatisch gebeurd en het dus "idiot proof" wordt.
weet iemand hier een mogelijkheid voor?

alvast bedankt

Gr. Jim

[pedorus]

Dat is bepaald niet eenvoudig. Datavalidatie vereist een aaneengesloten rij van cellen. Het kan wel. Stel dat je Excel 2007 hebt, de data met blanken staat in kolom A, en kolom B is een hulpkolom. Stel ook dat je de Engelse versie van Excel hebt, met Engelse taalinstellingen van je OS. Dan werkt het volgende:

Gebruik de name manager (onder formula) om twee namen te creëren:
BlanksRange als

code:

=$A$1:INDEX($A:$A,MATCH(REPT("z",255),$A:$A))

NoBlanksRange als:

code:

=$B$1:INDEX($B:$B,COUNTIF($B:$B,">"""))

In de hulpkolom, cel B1 zet je:

code:

=IFERROR(INDEX(BlanksRange,SMALL((IF(LEN(BlanksRange),
    ROW(INDIRECT("1:"&ROWS(BlanksRange))))),ROW(A1)),1),"")

(bron)

In te voeren als array-formula met ctrl-shift-enter zodat er {} omheen komt. Deze formule trek je vervolgens door totdat je denkt voldoende rijen te hebben, en dan trek je hem nog een stukje verder door.

En tot slot zet je in de gewenste kolom de bron voor de datavalidatie op =NoBlanksRange

Succes

[Verwijderd]

en er is ook de vba-oplossing : validatielijst zonder lege cellen
deze code bouwt via de bladwijziginggebeurtenis dynamisch de validatielijst op zonder lege waarden.

[slimjim]

ok top! bedankt voor de input.
dit ga ik eens even proberen.

(ik heb overigens windows 7 en met office 2007 nl)