gegevensvalidatie lege cel waarde negeren

[whitje]

(stap 1) Ik heb een lijst met daarin namen en daar achter een x tot welke groep ze behoren.

(stap 2) Vervolgens vraag ik in een apart bestand om de namen van een bepaalde groep.
Daar gebruik in nu een ALS functie voor.
Door de ALS functie worden ook de cellen die ik niet nodig heb (dus de namen die niet bij deze groep behoren) neergezet alleen dan als een cel zonder waarde.
Dus ik krijg dan een naam vervolgens 3 lege rijen en dan weer een naam.
Opzich geen probleem dat is te filteren.

(stap 3) Maar wanneer ik nu een gegevensvalidatie van deze lijst wil maken, krijg ik dus ook al die lege cellen te zien. Dus wanneer iemand nu de vervolgkeuzelijst kiest, ziet hij ook de naam en dan drie lege stukken en dan de volgende naam.
Ik heb wel gekozen voor lege cellen negeren, maar waarschijnlijk ziet hij het niet als lege cel omdat er een formule in staat, maar geen waarde.

Ik heb een klein voorbeeld bestand toegevoegd.
Met op blad 1: De namen en de groepen waartoe ze behoren
op blad 2: Een aparte groep 1 met de bijbehorende namen (de als-functie)
op blad 3: De gegevensvalidatie waarbij de lege cellen er dus ook bij staan.

Graag zou ik een oplossing hebben om of stap 2 op te lossen. dus dat ik sowieso geen lege cellen krijg wanneer ik de namen van een groep opvraag. Of stap 3, geen legen cellen in mijn vervolgkeuzelijst.

Eventueel mag het ook met vba maar ik wist geen oplossing?

 

[Warme bakkertje]

Na toepassing van het filter zal onderstaande de zichtbare cellen naar een andere locatie kopieëren waar je deze kan gebruiken om je validatielijst te vullen.

Sub tst()
Application.DisplayAlerts = False
With Sheets("Blad2")
    Set rng = Intersect(.AutoFilter.Range.EntireRow, Columns(1))
    Set rng1 = rng.Offset(1).Resize(rng.Rows.Count - 1, 1).SpecialCells(xlVisible)
    rng1.Copy [Blad3!A65536].End(xlUp).Offset(1)
End With
Application.DisplayAlerts = True
End Sub

 

[whitje]

zoals ik het begrijp is het bij deze oplossing zo dat het alleen werkt wanneer je de filter invoert, echter wil ik de filter er gewoon standaard oplaten staan.
wat ook een probleem is bij deze oplossing dat ik nu dus nogmaals een lijst krijg. Dus dan heb ik eerst een lijst met namen en lege regels en een filter er op en dan nog een lijst met alleen de namen die ik echt nodig heb en van die laatste kan ik dan de gegevensvalidatie maken. Dat worden iets te veel lijsten.
De aanpassing moet hem eingelijk zitten in, of de gegevensvalidatie dat die echt de lege regels negeert. Of bij stap 2 dat dat op een andere manier gaat zodat daar gelijk de lege regels al genegeert worden.

Kwam er ook niet zo goed uit waar ik deze code precies moest laten, om hem toch te proberen......sorry

 

[WHER]

Als je liever met formules werkt:

 

[Warme bakkertje]

Met deze heb je nog maar 1 klein bereikje nodig voor je validatie.

 

[whitje]

woooowww jullie zijn goochelaars

ik ga even kijken of ik het kan ontcijferen maar het werkt in ieder geval zoals ik wil :thumb:

 

[whitje]

Ik kom er toch niet helemaal uit

Ben verder gegaan met de formules van WHER. Vind dat andere wel mooier maar dat ga ik zeker niet snappen .

Opzich is de 'hulpkolom' overbodig en mogen de gegevens gelijk zoals in rij G worden genoteerd.
Ander probleem is wanneer ik iemand van groep verander dan krijg ik een #N/B in een cel en een naam te weinig?
Wat me opviel toen ik de formules wilden bekijken, dan klik ik erop en vervolgens klik ik in de formule balk. Wanneer ik dan op enter druk komt opnieuw de eerste naam er te staan. Dus bijv. ik klik op Wietse zijn naam. (die onderop bij groep 1 staat) en dan op die formulebalk dan op enter. dan komt er Anna te staan en is wietse uit de lijst verdwenen?

Heb bij het bestand nog 5 tabbladen toegevoegd. met op blad 'voorbeeld1' dezelfde lijst met namen en kruisjes. Op tabblad 'groep 1' zouden dan alleen de namen van groep 1 moeten komen zonder de lege regels. (ik heb het nu gewoon getypt om te laten zien ). Voor tabbladen 'groep 2' en 'groep 3' hetzelfde.

Hoop dat ik een beetje duidelijk overkom

Bedankt alvast maar weer!

 

[WHER]

whitje,

Opzich is de 'hulpkolom' overbodig en mogen de gegevens gelijk zoals in rij G worden genoteerd

Als jij me kan uitleggen hoe ik deze tussenstap kan overslaan, heb ik vandaag weer iets bijgeleerd.

en vervolgens klik ik in de formule balk. Wanneer ik dan op enter druk komt opnieuw de eerste naam er te staan

De formules in Blad1 G3:G12 zijn matrixformules, als je in de formulebalk klikt, moet je de formule opnieuw bevestigen met "ctrl+shift+enter", niet "enter" zoals een normale formule. Dat verklaart ook de vreemde/verkeerde resultaten die je daarna te zien krijgt.

Ander probleem is wanneer ik iemand van groep verander dan krijg ik een #N/B in een cel en een naam te weinig?

Als ik iemand van groep verander door in Blad1 B312 een "x" te verplaatsen, krijg ik geen #N/B, er staat dan een naam minder in de vorige groep en een naam meer in de huidige groep. Hoe verander jij iemand van groep?

In de bijlage zijn de formules voor blad "groep 1", "groep 2" enz.. ingevuld.

 

[Warme bakkertje]

Omdat je de andere zo mooi vond hier een nieuwe. De macro past zich automatisch aan, aan het aantal namen in kolom 1 en het aantal groepen in rij 1.

Sub tst()
    Application.DisplayAlerts = False
        With Sheets("Voorbeeld1")
            lrow = .Cells(Rows.Count, 1).End(xlUp).Row
            lcol = .Cells(1, Columns.Count).End(xlToLeft).Column
            For Each cl In .Range(.Cells(1, 2), .Cells(1, lcol))
                .AutoFilterMode = False
                    With .Range(.Cells(1, 2), .Cells(lrow, lcol))
                        .AutoFilter Field:=.Find(cl.Value, , xlValues, xlWhole).Column - 1, Criteria1:="<>"
                    End With
                Set rng = Intersect(.AutoFilter.Range.EntireRow, .Columns(1))
                Set rng1 = rng.Offset(1).Resize(rng.Rows.Count - 1, 1).SpecialCells(xlVisible)
                Sheets(cl.Value).Range("A2:A" & Sheets(cl.Value).Cells(Rows.Count, 1).End(xlUp).Row + 2).ClearContents
                rng1.Copy Sheets(cl.Value).Range("A2")
                .ShowAllData
                Next
        .AutoFilterMode = False
        End With
    Application.DisplayAlerts = True
End Sub

 

[whitje]

Als jij me kan uitleggen hoe ik deze tussenstap kan overslaan, heb ik vandaag weer iets bijgeleerd.

euuhm ik vrees dat ik jullie niets uit kan leggen ...helaas.
Maar zoals et nu is is het helemaal top ik kan er mee verder. gaat :thumb:.

Van die matrixformule, nog nooit mee gewerkt en toen ik het ging opzoeken in de help van excel en de eerste zin las wist ik ook waarom ik er nog nooit me gewerkt heb. Staat zoiets als , voor de zeer ervaren excel gebruiker.

En dat die het niet deed met het veranderen van de groep ik denk dat dat komt omdat ik dan toch iets veranderd heb zonder dat ik dat door heb gehad, want hij doet het inderdaad wel gewoon.

@ warme bakkertje: Ik vind het sowieso altijd mooier met vba en zou daar ook graag meer over leren. En moet zeggen beetje internet afstruinen naar voorbeelden en deze een beetje veranderen en je leert een hoop. Maar deze code, ik begrijp hem nog niet. Ga nog wel, als ik keer tijd heb, even kijken of ik het ga snappen, vind ik wel leuk. maar voor nu heb ik toch even gekozen voor de formules.