1

I'm trying to convert an Excel spreadsheet into Google Sheets. I have the entire year on a sheet and would like to filter the list based on a selected month from a dropdown. For example, selecting "June" would filter the page to only show June 1 through June 30.

Google Drive link: Google Sheets

OneDrive link: Excel Spreadsheet

I'm thinking I need to set the data validation of the dropdown to point to a secondary sheet and use a filter function to select dates with a position number corresponding to the month number (i.e. 1- January, 2-February, etc); but for some reason, my filter function returns an error message even if I attempt sorting by a manually entered date range ; (i.e.) date>=(2015,1,1), date<=(2015,1,31).

Edit: I just realized that OneDrive does not work with the VBA code. I'll attach it to this post:

Private Sub ComboBox1_Change() '2015 Sheet
Dim FromDate As Integer
Dim EndDate As Integer
Dim YearPortion As String
Dim NextYear As String

YearPortion1 = "-01-2015"
YearPortion2 = "-01-2015"

FromDate = ComboBox1.ListIndex + 1
EndDate = ComboBox1.ListIndex + 2
If EndDate = 13 Then YearPortion2 = "-01-2016"
If EndDate = 13 Then EndDate = 1
Sheet4.Cells(7, 1).CurrentRegion.Columns(1).AutoFilter 1, ">=" & Format(FromDate, "00") & YearPortion1, xlAnd, "<" & Format(EndDate, "00") & YearPortion2
End Sub
  • I tried you suggestion, I'm assuming that I'll need to put my filter on another sheet. Is the action that occurs in Excel by clicking on the month dropdown not possible in Google Sheets? – smileyninja May 21 '15 at 23:10
1

To convert YYYY, MM, DD to an actual Date object, you need the DATE command. The following works:

=filter(A6:F371,A6:A371>=DATE(2015,1,1),A6:A371<=DATE(2015,1,31))

But I see you want to filter based on dropdown entry. For this I would use MONTH function. For example, suppose cell J10 has validation rule: list of items

01 (January), 02 (February), 03 (March), and so on

Then your filter in J11 could be

=filter(A6:F371,MONTH(A6:A371)=VALUE(LEFT(J10,2)))

The idea here is that the names of month help the user in selection, but the filter uses the numeric format (month number).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.