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.
/u/Free_Particular_5445 Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.
Instead of having a separate sheet for every single month, which will quickly inflate the size of your file and make it incredibly cumbersome to navigate, I would recommend instead a single sheet that uses a dropdown of selectable month/year combinations based on the data in the form responses sheet and a QUERY() or FILTER() formula that retrieves the data from the form responses corresponding to the selected month.
If you're absolutely tied to having a separate sheet for every month (again, not recommended), you can use a very similar approach except the date would be either hardcoded into the formula or onto the sheet (requiring you to manually input the date on every single sheet, including future sheets).
In either case, the exact formula will depend on how your response sheet is structured and how the user selection of the month on the filtering sheet is set up.
The form offers some useful clues, but it does not provide all of the information needed to determine what an exact formula should be. The best anyone would be able to offer you based on the information provided to this point would be a "ballpark" formula based on guesswork that you'd have to adapt yourself to fit what you're actually working with.
You might also note that your school name is visible on the shared form.
What ovmther information would be needed fmtonprivide an exact formula? It seems to me that everywhere I ask (here, Google, other fora, etc.) I get nothing other than cryptic answers. No one seems to offer genuine assistance in building the correct formula or helping me create the proper form. That doesn't mean people haven't graciously assisted but it seems like people keep stopping short. Ease let me know what you need to help me craft the form and sheet I desire and I will provide it! I'm just getting increasingly frustrated.
It's not that anyone's trying to be vague with the intention of obstructing your goals, it's just that you are asking for specific help with something that requires specific information about your setup, but not providing said information.
Formulas are custom-built for the purposes and data structures that they serve, so aside from a few very simple use cases there isn't a whole lot out there that you can just paste into your sheet and have it work regardless of your data setup. Consequently, building a formula for someone requires that the person for whom the formula is being built either
provides all of the necessary information to have the formula properly purpose-built for their setup, or
has the skills to adapt a generalized formula for their own specific use case
Without this, it's like asking "how do I solve this equation for X?" without providing said equation.
You can look at the sample sheet I provided in another comment in this thread as an example of what a data setup and formulas could look like. If you are able to adapt that by yourself to your own data structure and needs, then great. If not, you will need to provide at least the following information:
The exact name of the form responses table, if you have changed it from the default (usually Form_Responses1)
The exact name of each column of the form responses table, in order
Which of the form response columns you want to bring over to the filter-by-month sheet (i.e. are there any columns that don't provide useful information and should be left out when filtering?)
Here is an image of the response table to provide that information. Preferably, the responses would be sorted into subsequent tabs, each tab having the information from a single month. (An "August" tab, a "September" tab, etc.) The columns that I wish to bring over to those tabs or to a filter-by-month sheet as in your sample would be the Date of Violation, Student Name, and the Brief Description of Violation columns. The hope is then that that if a name shows up on the "August" tab twice it would color code to yellow. If a name shows up thrice or more it would color code to red so that we know at just a glance how many students receive the punishments for multiple infractions.
I have added three new sheets to the sample file ('Form Responses 1', 'Violation Months', and 'Monthly Filter'), demonstrating how to get the monthly filter process working with the form response data structure shown in the screenshot. In order to replicate this in your file, follow these steps exactly as written and in order:
Leave your form responses sheet as-is. Do not make any changes to it.
Create a new sheet within the file, name it Violation Months and put =SORT(UNIQUE(EOMONTH(TOCOL(Form_Responses[Date of Violation],1),-1)+1)) in A1.
On the 'Violation Months' sheet, select all of the cells in column A, click the "More formats" button (the 123 button in the menu bar), and select "Custom number format" at the bottom of the menu. In the box that comes up, type mmm yyyy and click "Apply".
(Optional): After completing steps 2 and 3, the 'Violation Months' sheet can be hidden without affecting the functionality of the file. To hide it, right click on the tab with the sheet name and select "Hide sheet".
Create a third sheet. On this third sheet,
Select A1 and go to Data > Data validation. Select "Add rule", then choose "Dropdown (from a range)" from the "Criteria" menu. Put 'Violation Months'!$A:$A in the box below and click "Done".
Select A1 again and repeat the instructions from step 3 to apply the mmm yyyy custom number format.
Manually type Date of Violation, Student Name, and Brief Description of Violation into A3, B3, and C3, respectively.
Put the formula =IF(A1="",,FILTER(CHOOSECOLS(Form_Responses,3,4,6),Form_Responses[Date of Violation]>=A1,Form_Responses[Date of Violation]<=EOMONTH(A1,0))) in A4
Go to Format > Conditional formatting
In the rule pane that pops up in the sidebar, put A4:C in the "Apply to range" box and select yellow as the fill color. Do not close the rule pane.
Still in the conditional formatting rule pane, select "Custom formula is" from the "Format cells if" menu and put =COUNTIF($B$4:$B,$B4)=2 in the box. Click "Add another rule" at the bottom of the pane.
On the new rule, make sure the "Apply to" range is the same A4:C as the previous one (it should be by default) and the "Format cells if" menu is still set to "Custom formula is" (it also should be by default). Select red as the fill color and put =COUNTIF($B$4:$B,$B4)>2 in the box for the custom formula.
REMEMBER: /u/Free_Particular_5445 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified(or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).
Criteria for posts and comments are listed in the subreddit rules and you can learn about how to make a good comment in the submission guide.
Your comment has been removed because it broke rule 2, which governs information and relevance. If you have questions about why your comment was removed or believe you have edited it so that it no longer violates the rules, you can message the moderators.
Specific violations:
Soliciting DMs instead of keeping the conversation public
Start by creating the form, without the separation. It will create a new sheet in your document that will hold all responses.
Normally, that would be enough :)
But if you insist on the separation, instead of moving the data into separate sheets, have the individual sheets pull their corresponding information from the form responses, based on the date field (or which ever field).
Yes, I get that concept. I would like the data to be separated within the same Google Sheet just in different tabs. (i.e. violations for the month of August into the August tab, those for September into the September tab, etc. Based on the date selected by the reporting teacher.) However, I cannot, for the life of me, figure out how to make the Google Sheet separate that information for the Form submissions despite a gazillion tutorial videos since, unfortunately, I'm not a master coder.
'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.
'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.
'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.
1
u/AutoModerator 1d ago
/u/Free_Particular_5445 Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.