How can i get smart dropdown dates that look at other dates already used

[tonywatsonhelp]

Hi Everyone,
Now I thought this would be easy but as it turns out way past my abilitys. so i'm hoping some can help me.

We need to add in up to 30 start and end dates into a grid as shown below.

Column:	J	K	L
Row 8	Start Dates		End Dates
Row 9	drop down of all dates avaliable		Drop down of all dates avaliable after (Start Date)
10	same		same
11
12
13
14
15
16
and so on

so above hopefully shows what i'm trying to do,
I need to create To dropdown list these can go anywhere and can be as many rows columns and different as needed to create this list, but lets say the dropdowns will be after column AZ as this is all blank space

So if I was doing this in english, i would decribe it like this,
for start dates I would create a list of every date in 2022 (Called "Possible Start Dates"), then I would say to the formula or macro, Create a list of all the dates from "Possible Start Dates" that are not being used in grid J9 to L38, taking into account that these are start and end dates so any that are between should not show,

so my Start Date Dropdown list is all dates in 2022 that have not already been alocated.

then for End date drop down i would say, show all dates again but only if they are after the start date for this row.

I hope thats clear, I'm open to any ideas you might have and dont mind if its a Macro that get triggered on change, or a formula that creates my dropdown.

Please help if you can

Thanks

Tony

 

[BSALV]

in the module of that sheet

Private Sub Worksheet_Change(ByVal Target As Range)
     With Target
          If .Cells.Count > 1 Then Exit Sub                     'more then 1 cell changed = stop
          If Intersect(.Offset(0), Range("MyDropdowns")) Is Nothing Then Exit Sub     'nothing changed in your range with dates = stop
          If Len(.Value) = 0 Then Exit Sub                      'cell is empty = stop
          .Validation.Delete                                    'delete validation
          If .Value Like "?? ##-##" Then                        'content of the cell is like a "ddd dd-mm"-dateformat
               sp = Split(Split(.Value)(1), "-")                'split on the space and the "-" character
               .Value = DateSerial(Year(Date), sp(1), sp(0))    'replace cellvalue with the real date (long-variable)
          End If
     End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If Intersect(Target, Range("MyDropdowns")) Is Nothing Then Exit Sub     'your range with dates
     DropdownMacro                                              'add the validation in that cell
End Sub

in a module

Sub DropdownMacro()
     Dim DD, bStop

     Set c = Range("MyDropdowns")                               'your range with dates
     Set ac = ActiveCell.Cells(1, 1)

     Set c1 = Intersect(ac, c)                                  'intersect with that range and the activecell
     If c1 Is Nothing Then Exit Sub                             'no intersection= stop
     bStop = (ac.Column = c.Cells(1, 3).Column)                 'flag active cell is the stop-column
     If bStop And ac.Offset(, -2).Value = "" Then               'in stop-column and no start-date specified
          DD = "No dropdown ! Fill start date"
          GoTo MyValidation
     End If

     a = [SEQUENCE(,DATE(YEAR(TODAY())+1,1,1)-DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),1,1))]     'array of all the dates in this year
     For Each c In Range("MyDropdowns").Cells                   'loop through all the cells of "MyDropdowns"
          If Len(c.Value) > 0 Then                              'not empty
               r = Application.Match(c.Value2, a, 0)            'check if there is a match in a
               If IsNumeric(r) Then                             'match !
                    a(r) = "~+~"                                ' replace element with a dummy
               End If
          End If
     Next

     For i = 1 To UBound(a)                                     'loop through all elements of the array
          If bStop Then If a(i) <= ac.Offset(, -2).Value Then a(i) = "~+~"     'you're in the stop-column and the date is less or equal to startdate, then date = dummy
          a(i) = Format(a(i), "ddd dd-mm")                      'give it a real dateformat
     Next
     fl = Filter(Filter(a, "~+~", 0, vbTextCompare), "zondag", 0, vbTextCompare)     'filter the dummies, and for fun the "dutch Sundays" out of the array
     DD = Join(fl, ",")                                         'this 'll be the content of the dropdownlist, soon

MyValidation:
     With ActiveCell.Validation                                 'the validation of the active cell
          .Delete                                               'delet the former
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=DD     'add the list this macro made
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = "Choose a date"
          .ErrorTitle = "wrong choice"
          .InputMessage = "Choose a date in this list"
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
     End With
End Sub

The green cells have a normal dateformat, you can choose for example "ddd dd-mm" like i did or another one. The green cells is the defined name "MyDropdowns" !!!

xxxxxxxx.xlsb
	J	K	L
9
10
11
12	za 01-01		ma 03-01
13	zo 02-01		do 06-01
14	zo 09-01		di 11-01
15
16
17	vr 07-01
18			fill start date
19
20	wo 05-01		no dropdown
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Blad3

Cells with Data Validation
Cell	Allow	Criteria
J16	List	zaterdag 1 januari 2022;zondag 2 januari 2022;maandag 3 januari 2022;woensdag 5 januari 2022;donderdag 6 januari 2022;vrijdag 7 januari 2022;zaterdag 8 januari 2022;zondag 9 januari 2022;maandag 10 januari 2022;woensdag 12 januari 2022;donderdag 13 januari 2022;vrijdag 14 januari 2022;zaterdag 15 januari 2022;zondag 16 januari 2022;maandag 17 januari 2022;woensdag 19 januari 2022;zondag 6 februari 2022;maandag 7 februari 2022;woensdag 9 februari 2022;donderdag 10 februari 2022;vrijdag 11 februari 2022;zaterdag 12 februari 2022;zondag 13 februari 2022;maandag 14 februari 2022;woensdag 16 februari 2022;donderdag 17 februari 2022;vrijdag 18 februari 2022;zaterdag 19 februari 2022;zondag 20 februari 2022;maandag 21 februari 2022;woensdag 23 februari 2022;donderdag 24 februari 2022;vrijdag 25 februari 2022;zaterdag 26 februari 2022;zondag 27 februari 2022;maandag 28 februari 2022;woensdag 2 maart 2022;donderdag 3 maart 2022;vrijdag 4 maart 2022;zaterdag 5 maart 2022;zondag 6 maart 2022;maandag 7 maart 2022;woensdag 9 maart 2022;donderdag 10 maart 2022;vrijdag 11 maart 2022;zaterdag 12 maart 2022;zondag 13 maart 2022;maandag 14 maart 2022;woensdag 16 maart 2022;donderdag 17 maart 2022;vrijdag 18 maart 2022;zaterdag 19 maart 2022;zondag 20 maart 2022;maandag 21 maart 2022;woensdag 23 maart 2022;donderdag 24 maart 2022;vrijdag 25 maart 2022;zaterdag 26 maart 2022;zondag 27 maart 2022;maandag 28 maart 2022;woensdag 30 maart 2022;donderdag 31 maart 2022;vrijdag 1 april 2022;zaterdag 2 april 2022;zondag 3 april 2022;maandag 4 april 2022;woensdag 6 april 2022;donderdag 7 april 2022;vrijdag 8 april 2022;zaterdag 9 april 2022;zondag 10 april 2022;maandag 11 april 2022;woensdag 13 april 2022;donderdag 14 april 2022;vrijdag 15 april 2022;zaterdag 16 april 2022;zondag 17 april 2022;maandag 18 april 2022;woensdag 20 april 2022;donderdag 21 april 2022;vrijdag 22 april 2022;zaterdag 23 april 2022;zondag 24 april 2022;maandag 25 april 2022;woensdag 27 april 2022;donderdag 28 april 2022;vrijdag 29 april 2022;zaterdag 30 april 2022;zondag 1 mei 2022;maandag 2 mei 2022;woensdag 4 mei 2022;donderdag 5 mei 2022;vrijdag 6 mei 2022;zaterdag 7 mei 2022;zondag 8 mei 2022;maandag 9 mei 2022;woensdag 11 mei 2022;donderdag 12 mei 2022;vrijdag 13 mei 2022;zaterdag 14 mei 2022;zondag 15 mei 2022;maandag 16 mei 2022;woensdag 18 mei 2022;donderdag 19 mei 2022;vrijdag 20 mei 2022;zaterdag 21 mei 2022;zondag 22 mei 2022;maandag 23 mei 2022;woensdag 25 mei 2022;donderdag 26 mei 2022;vrijdag 27 mei 2022;zaterdag 28 mei 2022;zondag 29 mei 2022;maandag 30 mei 2022;woensdag 1 juni 2022;donderdag 2 juni 2022;vrijdag 3 juni 2022;zaterdag 4 juni 2022;zondag 5 juni 2022;maandag 6 juni 2022;woensdag 8 juni 2022;donderdag 9 juni 2022;vrijdag 10 juni 2022;zaterdag 11 juni 2022;zondag 12 juni 2022;maandag 13 juni 2022;woensdag 15 juni 2022;donderdag 16 juni 2022;vrijdag 17 juni 2022;zaterdag 18 juni 2022;zondag 19 juni 2022;maandag 20 juni 2022;woensdag 22 juni 2022;donderdag 23 juni 2022;vrijdag 24 juni 2022;zaterdag 25 juni 2022;zondag 26 juni 2022;maandag 27 juni 2022;woensdag 29 juni 2022;donderdag 30 juni 2022;vrijdag 1 juli 2022;zaterdag 2 juli 2022;zondag 3 juli 2022;maandag 4 juli 2022;woensdag 6 juli 2022;donderdag 7 juli 2022;vrijdag 8 juli 2022;zaterdag 9 juli 2022;zondag 10 juli 2022;maandag 11 juli 2022;woensdag 13 juli 2022;donderdag 14 juli 2022;vrijdag 15 juli 2022;zaterdag 16 juli 2022;zondag 17 juli 2022;maandag 18 juli 2022;woensdag 20 juli 2022;donderdag 21 juli 2022;vrijdag 22 juli 2022;zaterdag 23 juli 2022;zondag 24 juli 2022;maandag 25 juli 2022;woensdag 27 juli 2022;donderdag 28 juli 2022;vrijdag 29 juli 2022;zaterdag 30 juli 2022;zondag 31 juli 2022;maandag 1 augustus 2022;woensdag 3 augustus 2022;donderdag 4 augustus 2022;vrijdag 5 augustus 2022;zaterdag 6 augustus 2022;zondag 7 augustus 2022;maandag 8 augustus 2022;woensdag 10 augustus 2022;donderdag 11 augustus 2022;vrijdag 12 augustus 2022;zaterdag 13 augustus 2022;zondag 14 augustus 2022;maandag 15 augustus 2022;woensdag 17 augustus 2022;donderdag 18 augustus 2022;vrijdag 19 augustus 2022;zaterdag 20 augustus 2022;zondag 21 augustus 2022;maandag 22 augustus 2022;woensdag 24 augustus 2022;donderdag 25 augustus 2022;vrijdag 26 augustus 2022;zaterdag 27 augustus 2022;zondag 28 augustus 2022;maandag 29 augustus 2022;woensdag 31 augustus 2022;donderdag 1 september 2022;vrijdag 2 september 2022;zaterdag 3 september 2022;zondag 4 september 2022;maandag 5 september 2022;woensdag 7 september 2022;donderdag 8 september 2022;vrijdag 9 september 2022;zaterdag 10 september 2022;zondag 11 september 2022;maandag 12 september 2022;woensdag 14 september 2022;donderdag 15 september 2022;vrijdag 16 september 2022;zaterdag 17 september 2022;zondag 18 september 2022;maandag 19 september 2022;woensdag 21 september 2022;donderdag 22 september 2022;vrijdag 23 september 2022;zaterdag 24 september 2022;zondag 25 september 2022;maandag 26 september 2022;woensdag 28 september 2022;donderdag 29 september 2022;vrijdag 30 september 2022;zaterdag 1 oktober 2022;zondag 2 oktober 2022;maandag 3 oktober 2022;woensdag 5 oktober 2022;donderdag 6 oktober 2022;vrijdag 7 oktober 2022;zaterdag 8 oktober 2022;zondag 9 oktober 2022;maandag 10 oktober 2022;woensdag 12 oktober 2022;donderdag 13 oktober 2022;vrijdag 14 oktober 2022;zaterdag 15 oktober 2022;zondag 16 oktober 2022;maandag 17 oktober 2022;woensdag 19 oktober 2022;donderdag 20 oktober 2022;vrijdag 21 oktober 2022;zaterdag 22 oktober 2022;zondag 23 oktober 2022;maandag 24 oktober 2022;woensdag 26 oktober 2022;donderdag 27 oktober 2022;vrijdag 28 oktober 2022;zaterdag 29 oktober 2022;zondag 30 oktober 2022;maandag 31 oktober 2022;woensdag 2 november 2022;donderdag 3 november 2022;vrijdag 4 november 2022;zaterdag 5 november 2022;zondag 6 november 2022;maandag 7 november 2022;woensdag 9 november 2022;donderdag 10 november 2022;vrijdag 11 november 2022;zaterdag 12 november 2022;zondag 13 november 2022;maandag 14 november 2022;woensdag 16 november 2022;donderdag 17 november 2022;vrijdag 18 november 2022;zaterdag 19 november 2022;zondag 20 november 2022;maandag 21 november 2022;woensdag 23 november 2022;donderdag 24 november 2022;vrijdag 25 november 2022;zaterdag 26 november 2022;zondag 27 november 2022;maandag 28 november 2022;woensdag 30 november 2022;donderdag 1 december 2022;vrijdag 2 december 2022;zaterdag 3 december 2022;zondag 4 december 2022;maandag 5 december 2022;woensdag 7 december 2022;donderdag 8 december 2022;vrijdag 9 december 2022;zaterdag 10 december 2022;zondag 11 december 2022;maandag 12 december 2022;woensdag 14 december 2022;donderdag 15 december 2022;vrijdag 16 december 2022;zaterdag 17 december 2022;zondag 18 december 2022;maandag 19 december 2022;woensdag 21 december 2022;donderdag 22 december 2022;vrijdag 23 december 2022;zaterdag 24 december 2022;zondag 25 december 2022;maandag 26 december 2022;woensdag 28 december 2022;donderdag 29 december 2022;vrijdag 30 december 2022;zaterdag 31 december 2022
L9	List	no dropdown; fill start date
L16	List	no dropdown; fill start date
L18	List	No dropdown ! Fill start date
J9	List	di 04-01;za 08-01;ma 10-01;wo 12-01;do 13-01;vr 14-01;za 15-01;zo 16-01;ma 17-01;di 18-01;wo 19-01;do 20-01;vr 21-01;za 22-01;zo 23-01;ma 24-01;di 25-01;wo 26-01;do 27-01;vr 28-01;za 29-01;zo 30-01;ma 31-01;di 01-02;wo 02-02;do 03-02;vr 04-02;za 05-02;zo 06-02;ma 07-02;di 08-02;wo 09-02;do 10-02;vr 11-02;za 12-02;zo 13-02;ma 14-02;di 15-02;wo 16-02;do 17-02;vr 18-02;za 19-02;zo 20-02;ma 21-02;di 22-02;wo 23-02;do 24-02;vr 25-02;za 26-02;zo 27-02;ma 28-02;di 01-03;wo 02-03;do 03-03;vr 04-03;za 05-03;zo 06-03;ma 07-03;di 08-03;wo 09-03;do 10-03;vr 11-03;za 12-03;zo 13-03;ma 14-03;di 15-03;wo 16-03;do 17-03;vr 18-03;za 19-03;zo 20-03;ma 21-03;di 22-03;wo 23-03;do 24-03;vr 25-03;za 26-03;zo 27-03;ma 28-03;di 29-03;wo 30-03;do 31-03;vr 01-04;za 02-04;zo 03-04;ma 04-04;di 05-04;wo 06-04;do 07-04;vr 08-04;za 09-04;zo 10-04;ma 11-04;di 12-04;wo 13-04;do 14-04;vr 15-04;za 16-04;zo 17-04;ma 18-04;di 19-04;wo 20-04;do 21-04;vr 22-04;za 23-04;zo 24-04;ma 25-04;di 26-04;wo 27-04;do 28-04;vr 29-04;za 30-04;zo 01-05;ma 02-05;di 03-05;wo 04-05;do 05-05;vr 06-05;za 07-05;zo 08-05;ma 09-05;di 10-05;wo 11-05;do 12-05;vr 13-05;za 14-05;zo 15-05;ma 16-05;di 17-05;wo 18-05;do 19-05;vr 20-05;za 21-05;zo 22-05;ma 23-05;di 24-05;wo 25-05;do 26-05;vr 27-05;za 28-05;zo 29-05;ma 30-05;di 31-05;wo 01-06;do 02-06;vr 03-06;za 04-06;zo 05-06;ma 06-06;di 07-06;wo 08-06;do 09-06;vr 10-06;za 11-06;zo 12-06;ma 13-06;di 14-06;wo 15-06;do 16-06;vr 17-06;za 18-06;zo 19-06;ma 20-06;di 21-06;wo 22-06;do 23-06;vr 24-06;za 25-06;zo 26-06;ma 27-06;di 28-06;wo 29-06;do 30-06;vr 01-07;za 02-07;zo 03-07;ma 04-07;di 05-07;wo 06-07;do 07-07;vr 08-07;za 09-07;zo 10-07;ma 11-07;di 12-07;wo 13-07;do 14-07;vr 15-07;za 16-07;zo 17-07;ma 18-07;di 19-07;wo 20-07;do 21-07;vr 22-07;za 23-07;zo 24-07;ma 25-07;di 26-07;wo 27-07;do 28-07;vr 29-07;za 30-07;zo 31-07;ma 01-08;di 02-08;wo 03-08;do 04-08;vr 05-08;za 06-08;zo 07-08;ma 08-08;di 09-08;wo 10-08;do 11-08;vr 12-08;za 13-08;zo 14-08;ma 15-08;di 16-08;wo 17-08;do 18-08;vr 19-08;za 20-08;zo 21-08;ma 22-08;di 23-08;wo 24-08;do 25-08;vr 26-08;za 27-08;zo 28-08;ma 29-08;di 30-08;wo 31-08;do 01-09;vr 02-09;za 03-09;zo 04-09;ma 05-09;di 06-09;wo 07-09;do 08-09;vr 09-09;za 10-09;zo 11-09;ma 12-09;di 13-09;wo 14-09;do 15-09;vr 16-09;za 17-09;zo 18-09;ma 19-09;di 20-09;wo 21-09;do 22-09;vr 23-09;za 24-09;zo 25-09;ma 26-09;di 27-09;wo 28-09;do 29-09;vr 30-09;za 01-10;zo 02-10;ma 03-10;di 04-10;wo 05-10;do 06-10;vr 07-10;za 08-10;zo 09-10;ma 10-10;di 11-10;wo 12-10;do 13-10;vr 14-10;za 15-10;zo 16-10;ma 17-10;di 18-10;wo 19-10;do 20-10;vr 21-10;za 22-10;zo 23-10;ma 24-10;di 25-10;wo 26-10;do 27-10;vr 28-10;za 29-10;zo 30-10;ma 31-10;di 01-11;wo 02-11;do 03-11;vr 04-11;za 05-11;zo 06-11;ma 07-11;di 08-11;wo 09-11;do 10-11;vr 11-11;za 12-11;zo 13-11;ma 14-11;di 15-11;wo 16-11;do 17-11;vr 18-11;za 19-11;zo 20-11;ma 21-11;di 22-11;wo 23-11;do 24-11;vr 25-11;za 26-11;zo 27-11;ma 28-11;di 29-11;wo 30-11;do 01-12;vr 02-12;za 03-12;zo 04-12;ma 05-12;di 06-12;wo 07-12;do 08-12;vr 09-12;za 10-12;zo 11-12;ma 12-12;di 13-12;wo 14-12;do 15-12;vr 16-12;za 17-12;zo 18-12;ma 19-12;di 20-12;wo 21-12;do 22-12;vr 23-12;za 24-12;zo 25-12;ma 26-12;di 27-12;wo 28-12;do 29-12;vr 30-12;za 31-12

 

[tonywatsonhelp]

Thank you BSALV this looks great, i'll give it a try, thanks for all your hard work
Tony