coachspeez
New Member
- Joined
- Sep 8, 2013
- Messages
- 3
Hey guys,
First and foremost, forgive my breach of protocol if I have posted this in the incorrect place. While this is technically a Google Sheet question, I have found their help forums to be of no help.
I've created a Google Form for my school that tracks the names of students who haven't turned in homework assignments. Teachers submit names via the Form and the names populate the connected spreadsheet as usual. I have made the Form editable so that once the student completes the assignment, the teacher can re-access the form (via the link that is emailed to them after their original submission) and "remove" the student's name from the form, and thus, the spreadsheet. I put "remove" in quotes because the student's name isn't actually removed; the drop down lists containing all the students' names have a first option titled "CLEARED". Teachers select this option so that the spreadsheet will display the word CLEARED where the student's name used to be.
For example, the student listed in H2 (above) has not completed the assignment yet, so his name remains in that cell. I2 did contain a student name, however now it reads CLEARED because that student turned in the assignment. His teacher re-accessed the original form and changed the drop down that contained his name to CLEARED so that his name no longer populates the naughty list.
The spreadsheet linked to the Google Form serves various functions based on a few different formulas I have set up (to the best of my ability, being a relative n00b to this kind of thing). All of the students in our school are listed in the spreadsheet (one worksheet per grade level for 6th, 7th, and 8th grade) so that I can use VLOOKUP and a few other functions when needed.
Here's the rub: I wanted to make a column on the worksheets containing the students' names that tracks how many times their name has been submitted via one of the Google Forms. A simple COUNTIF formula would do the trick, I thought to myself. BUT....this of course won't work, because once the teacher edits his original submission (the form containing the names) to "clear" students who have turned in the assignment (thus removing the kid's name and changing it to CLEARED) the column containing the COUNTIF formula will simply revert back to zero because the student's name is no longer on the naughty list.
At long last, my question: is it possible to set up the spreadsheet in such a way that when a student's name is submitted, it stays somewhere in the spreadsheet (perhaps it is copied to a new location?) even after the teacher changes the form? In other words, when H2 is changed to CLEARED in the above example because Mr. Richter finally decided to get off his duff and do his homework, can I still have some record in the spreadsheet that his name did appear on the list at one point?
Hope that makes sense. Please feel free to let me know if you guys need clarification. While I'm no VBA expert, I've surprised myself with how much I've been able to figure out on my own; but relative simplicity in the language of your MUCH APPRECIATED response would be...well, much appreciated.
Cheers,
Chris Spisak
(Working on Mac OS X 10.8.4 just now, but will be using the form with Windows 7 at my school)
First and foremost, forgive my breach of protocol if I have posted this in the incorrect place. While this is technically a Google Sheet question, I have found their help forums to be of no help.
I've created a Google Form for my school that tracks the names of students who haven't turned in homework assignments. Teachers submit names via the Form and the names populate the connected spreadsheet as usual. I have made the Form editable so that once the student completes the assignment, the teacher can re-access the form (via the link that is emailed to them after their original submission) and "remove" the student's name from the form, and thus, the spreadsheet. I put "remove" in quotes because the student's name isn't actually removed; the drop down lists containing all the students' names have a first option titled "CLEARED". Teachers select this option so that the spreadsheet will display the word CLEARED where the student's name used to be.
For example, the student listed in H2 (above) has not completed the assignment yet, so his name remains in that cell. I2 did contain a student name, however now it reads CLEARED because that student turned in the assignment. His teacher re-accessed the original form and changed the drop down that contained his name to CLEARED so that his name no longer populates the naughty list.
The spreadsheet linked to the Google Form serves various functions based on a few different formulas I have set up (to the best of my ability, being a relative n00b to this kind of thing). All of the students in our school are listed in the spreadsheet (one worksheet per grade level for 6th, 7th, and 8th grade) so that I can use VLOOKUP and a few other functions when needed.
Here's the rub: I wanted to make a column on the worksheets containing the students' names that tracks how many times their name has been submitted via one of the Google Forms. A simple COUNTIF formula would do the trick, I thought to myself. BUT....this of course won't work, because once the teacher edits his original submission (the form containing the names) to "clear" students who have turned in the assignment (thus removing the kid's name and changing it to CLEARED) the column containing the COUNTIF formula will simply revert back to zero because the student's name is no longer on the naughty list.
At long last, my question: is it possible to set up the spreadsheet in such a way that when a student's name is submitted, it stays somewhere in the spreadsheet (perhaps it is copied to a new location?) even after the teacher changes the form? In other words, when H2 is changed to CLEARED in the above example because Mr. Richter finally decided to get off his duff and do his homework, can I still have some record in the spreadsheet that his name did appear on the list at one point?
Hope that makes sense. Please feel free to let me know if you guys need clarification. While I'm no VBA expert, I've surprised myself with how much I've been able to figure out on my own; but relative simplicity in the language of your MUCH APPRECIATED response would be...well, much appreciated.
Cheers,
Chris Spisak
(Working on Mac OS X 10.8.4 just now, but will be using the form with Windows 7 at my school)
