Step 1: Set Up the Spreadsheet

On a separate tab
• Column A: place all answers to a drop down question in Column A (Title Column Pre-Count)
• Column B: use the countif formula to search the response sheet to count if an answer has been selected. (Title Column Count)

=COUNTIF('Form Responses 1'!D:D,A2)

This formula translates to:
1. "=" Hey Cell, you will equal the following

2. "COUNTIF('Form Responses 1'!D:D" Hey Cell, look in the form response sheet, column D, and count...

3. ",A2)" This is what we want the cell to count - In A2 will be the answer you want to eliminate.
• Column C: Use the =If formula to determine if an answer should be posted of if it should be eliminated. (Title Column Post)

=IF(B2>0,"",A2)

This formula translates to:
1. "=" Hey cell, you will equal something

2. "IF(B2>0,""...Hey Cell, if cell B2 equals larger than zero, then someone must have selected this answer already, then you should equal "" - which means you equal nothing, therefore, nothing will post to the form from this cell.

3. ",A2)" Hey cell, if B2 is greater than zero, post nothing, but if not, post A2.

Step 2: Use FormRanger on the Form
Now that the spreadsheet is counting responses, and willing to eliminate answers once they have been selected, we just ask FormRanger to pull answers off the sheet into the form.

On the form, use the add on Form Ranger.
• Launch Form Ranger
• Follow steps to assign the "Post" Column as the answers to a multiple choice/drop down question.

UBER-EXTRA CREDIT
3. Crowdsourcing Appreciation
The big Merge: imagine sending out a link to hundreds of families asking them to appreciate present and past teachers, and then thousands of feedback came pouring in. Well, in about 15 minutes you could merge all the feedback for each person into one cell on the spreadsheet, then mailmerge that to an email for each staff member. I call this, the big merge.

Click this Blogpost for the step by step details.

CapCue Board Member