Form Ranger - Auto Eliminate

Imagine you want 10 people to pick just one time to come see you: You could send an email and ask everyone to pick a time and reply all? Then each person would have to fish through emails to determine which time slot had been taken. Or you could send around a paper and pencil, but the last one to receive the clipboard gets last pick.

Practical Solution:

Create a Google Form with open time slots. Each participant logs into the form, selects a time that works for them, and when they click submit, the form auto-refreshes and removes the selected time from the list.

1) Create the Form:

Participants can enter name and email, or it can be automatically retrieved upon login. In this screenshot, we'll use Time A - D as our "Times to Select."

2) Install FormRanger on the Response Form Google Sheet.

3) Create a second sheet and use the following headings for Columns A through C:

A - PreRanger, B - Count Responses, C - Ranger Post. The headings will help us keep organized as we go along.

4) In Column A, create the list of times you want posted to your form.

5) In Column B, use this formula to count how many times "Time A" appears in the appropriate response column on the Response Form Sheet. When you enter the formula, you should get a result of "0" since no responses have yet been recorded.

Note: This formula translates like this - This Cell is equal to the number of times something can be counted(On the sheet titled 'Form Responses' in Column C from C2 to C100, this is what is being counted - how many times it finds the exact language from A2, which in this case is "Time A".

6) In Column C, use this formula to tell the cells in Column C what to post - In this case, if the value in cell B2 is greater than zero (meaning that this time-slot has been selected) then show "Filled", whereas if B2 = Zero, then fill this cell with what is written in A2.

What is nice about this formula is that it allows Column A to remain static and Column C to change based on the value of Column B. It is Column C that will be posting to your form.

7) Drag the formulas down for all your selections.

8) Run Form Ranger and select Column C as the Column to pull your entries from.

9) Set trigger to be upon submission.

Now test it: When you submit the form, and go back to the form a second time, it will show "Filled" for the time slot you have selected.

To unselect a time after you test it:

1) Go to the Form Response Sheet and delete the line containing your submission.

2) Refresh form.