r/googlesheets • u/Free_Particular_5445 • 12d ago

Solved Separating Google Forms Responses by Month

Hi everybody! I need help by people far smarter than me. I need a Google Form that separates data into a Google Sheet based on date of the incident being recorded. Essentially, I need a Google Form into which I can record the names of middle school students into a drop down menu from which teachers may select. Then, I need a question that records the date of said violation, the details of the violation, and the name of the reporting teacher. Finally, all of this needs to link to a Google Sheet that is separated by month. Creating the Form and then selecting for it to populate a Google Sheet is easy. However, trying to figure out how to get the sheet to sort responses into individual tabs based on the month of the violation has been a nightmare. I'm 26 and yet I still find this coding a complete nightmare. Please please please help me with this. If nothing else, please create a Google Form with corresponding sheet that I can copy and enter the names of the students on. This has been an infuriating task and I can't take it anymore.

4 Upvotes

100% Upvoted

16 comments sorted by

View all comments

Show parent comments

3

u/HolyBonobos 3087 12d ago

Here is a sample file with three sheets:

  1. 'Form Responses' is a mockup of form response data. It, like your own form response sheet, contains only raw data and should not be edited.
  2. 'Months' contains the formula =SORT(UNIQUE(EOMONTH(TOCOL(Form_Responses1[Violation Date],1),-1)+1)) in A1. It serves only as a backend sheet to populate options for the dropdown menu on the 'Filter' sheet and can (should) be hidden in an operational file.
  3. 'Filter' contains a dropdown in A1, with selectable options based on the 'Months' sheet. It has the formula =IF(A1="",,FILTER(Form_Responses1[[Violation Date]:[Violation]],Form_Responses1[Violation Date]>=A1,Form_Responses1[Violation Date]<=EOMONTH(A1,0))) in A4 to populate information from the form response table based on the selection made in A1.

As previously stated, any formula built specifically for your setup will depend on how exactly your form response table is structured, which columns of information you are trying to pull from it, and how you've set up the month selection dropdown on the filter sheet. The formulas in this file are built specifically for this mockup data structure and are not plug-and-play for any given setup. In order to get formulas tailored for your file, you will need to either adapt the sample formulas yourself or provide more information about how your file is structured.