Select month from Data Validation List

[robertvdb]

I have a sheet, in Col A are the dates, in Col B are the hours worked.

I want to show monthly totals in cell I3. For March, the total would be 15.

In G1, I want to show a Data Validation List which ONLY shows the months listed in Col A. In this example, only 01/2023 through 04/2023.

So I guess I need a dynamic Source for that list. How to realise this (with or without VBA) ?

 

[Legacy 143009]

If I were you I would input my month numbers into validation list manually like: 1,2,3,4,5,6,7,8,9,10,11,12

Then this formula should work fine:

=SUMPRODUCT(--(MONTH(A$2:A$31)=$G$1),B$2:B$31)

 

[RustamMika]

Hi,
With VBA:

Sub MMM()
    With CreateObject("System.Collections.ArrayList")
            For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
                    ss = Format(Cells(i, 1).Value, "mmmm yyyy")
                    If Not .Contains(ss) Then .Add ss
            Next
            .Sort
            ss = Join(.toarray, ",")
    End With
     With [G1].Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=ss
    End With
End Sub

 

[robertvdb]

Thanks Flashbond, I guess you will also agree with the code provided by Rustam.

Rustam, is there any way to Sort the list in descending order, i.e. the latest month on top ?

 

[RustamMika]

to Sort the list in descending order, i.e. the latest month on top ?

Yes ,
Possible..

Sub MMM()
    With CreateObject("System.Collections.ArrayList")
            For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
                    ss = Format(Cells(i, 1).Value, "mmmm yyyy")
                    If Not .Contains(ss) Then .Add ss
            Next
            .Sort
            .Reverse
            ss = Join(.toarray, ",")
    End With
     With [G1].Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=ss
    End With
End Sub

 

[Legacy 143009]

Thanks Flashbond, I guess you will also agree with the code provided by Rustam.

Ofcourse, also this is another solution. No problem

 

[robertvdb]

thanks to both of you. Both solutions are fine, but in this particular case the VBA code better suits my needs.