Variabele in validatielijst

[Jand66]

In het bestand zijn 2 tabbladen. De eerste is het blad waar de planningen in moeten komen en de tweede zijn de barleden die ingepland kunnen worden.

In het tweede tabblad staan alle mogelijke bardiensten van een week. (standaard is de bar alleen op do., vri., en zaterdag open, bi uitzondering op andere avonden). Op sheet 2 zijn al deze bardiensten gekoppeld aan een Gedefinieerde naam. Zie Formules > Namen beheren. Deze Gedefinieerde namen moeten als dropdown lijsten gebruikt worden in de blad 1 in kolom E.
Normaliter maak je een dropdownlist met Gegevens > Gegevensvalidatie. Door te kiezen voor Lijst en de naam van het bereik bij Bron in te vullen is het klaar. Maar dan verwijst de cel altijd naar een vast bereik.

Wat ik wil is het volgende:
In kolom D van blad 1 staan de diensten. Ik wil bij Gegevensvalidatie in kolom E de bron laten verwijzen naar de naastgelegen cel uit kolom D, deze zijn namelijk gelijk aan de Gedefinieerde namen van blad 3
Voorbeeld:
Dus bij Gegevensvalidatie van cel E3 selecteer ik lijst en zou ik vervolgens willen verwijzen naar cel D3, waardoor ik in de dropdownlist de namen krijg te zien die horen bij de Gedefinieerde naam Dinsdagavond van blad 2. Voor cel E7 wordt dan verwezen naar D7 en krijg ik de namen van de Gedefinieerde naam Zaterdagochtend van blad 3 te zien.

Probleem:
Ik weet niet hoe ik bij Gegevensvalidatie > bron hoe ik via een cel in kolom D kan verwijzen naar de Gedefinieerde namen in blad 3.

Wie o wie weet hiervoor een oplossing. Dit hoeft uiteraard niet beslist via Gegevensvalidatie. Als het ook kan via Vba vind ik dat ook prima, maar ik heb mijn hoofd hierover al gebroken, zowel via gegevensvalidatie als met Vba. Maar het is tot nu toe niet gelukt een dropdownlist te maken die gebaseerd is op de waarden in kolom D.

 

[HSV]

Zoek eens met de zoekfunctie van dit forum naar "Getrapte validatie".
Daar kom je vast wel uit.

 

[Jand66]

@HSV,

Deze had ik al gezien, maar dat is niet hetzelfde. Wat ik overigens in dat bestand niet kan vinden is waar de definitie van CurrentRegion is gedefinieerd. Misschien dat ik daarmee nog wel iets kan.

 

[popipipo]

"Getrapte validatie".

Dit werkt alleen met een vast bereik en niet met een variabel bereik

Maak van dindagavond dus:

='Voorkeuren bar'!$B$1:$B$25

ipv

=OFFSET('Voorkeuren bar'!$B$2;0;0;COUNTA('Voorkeuren bar'!$B:$B)-1;1)

 

[HSV]

@popipipo; Een gedefinieerd bereik kan variabel zijn.

 

[popipipo]

Maar dan werkt de indirect methode niet meer

 

[VenA]

Maar een beetje code er tegenaan gooien dan?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
    If Target.Column = 5 And Target.Row > 2 And Target.Offset(, -1) <> "" Then
        ar = Sheets("Voorkeuren bar").Cells(1).CurrentRegion
        For j = 1 To UBound(ar, 2)
            If LCase(ar(1, j)) = LCase(Target.Offset(, -1).Value) Then
                For jj = 2 To UBound(ar)
                    If ar(jj, j) <> "" Then c00 = c00 & "," & ar(jj, j) Else Exit For
                Next jj
            End If
        Next j
        If Len(c00) > 0 Then
            With Target.Validation
                .Delete
                .Add xlValidateList, , , Mid(c00, 2)
            End With
        End If
    End If
End If
End Sub

En voor het dagdeel

Function getDagdeel(Datum As Date, Tijd As Date) As String
  getDagdeel = Format(Weekday(Datum, 1), "dddd") & Application.Lookup(Hour(Tijd), Array(8, 17, 18), Array("ochtend", "middag", "avond"))
End Function

 

[HSV]

Geen Vba voor nodig.

 

[Jand66]

HSV en VenA bedankt voor beide oplossingen. Werken beide perfect.

@HSV,
MBT tot mijn vraag mbt currentregion. Ik was hier even helemaal abuis. Dat is de normale excel functie. Ik las het echter, omdat het voorbeeld ging over regio's, dat ze daar de huidige geselecteerde regio mee bedoelden... Volgende keer zal ik eerste iets beter lezen voor ik een vraag stel

 

[HSV]

Maakt niet uit hoor; gewoon vragen Jan.

Een currentregion is een aaneengesloten bereik vanaf een bepaalde cel die je aangeeft in de code (kan activecell zijn of een benoemde cel in de code).

Uitleg Help Excel2007.

[TABLE="width: 100%"]
[TR]
[TD="align: left"]Range.CurrentRegion, eigenschap[/TD]
[/TR]
[/TABLE]

Deze eigenschap geeft als resultaat een Range-object dat het huidige gebied voorstelt. Het huidige gebied is een bereik dat wordt begrensd door een combinatie van lege rijen en lege kolommen. Alleen-lezen.