I was approached about helping to organize the volunteer sign-in sheet. The Admin Assistant had already created a Google Form to collect information. There was a Google Spreadsheet set to collect the information from the Form. Then, there was a tab per day to copy and paste to. (* Copy and pasting is rife with unintentional error possibilities.)

What they really wanted at the end was a sign-in sheet that could be printed. As the volunteers come in, they could physically sign in. There are also additional forms that need to be in place. So, the desire was a sign-in sheet that looked something like this:

Google Spreadsheet tab with the following columns: Time In ,Time Out,Classroom/ Teacher,Last Name,First Name,Volunteer Awareness Training Completed,Volunteer Registration Form Completed,Criminal Background Check Completed,Event,Volunteer Signature

Since I prefer for these things to be automated, I started by creating an additional column for the Date. This date is auto-populated from the spreadsheet. By using Data Validation, this can then be a drop-down. No more additional tabs for the spreadsheet, simply pick the date and print.

We now have two tabs:

  • Form Responses – where the Form Responses automatically go
  • Print Date – this is the printable sheet.

I selected the “Print Date” tab. To set up the Date, I clicked into Cell A2 (this is where I want the pickable date). Then, I went to Data | Data validation. In Apply to range, I chose ‘Print Date’!A2 (note the single quote marks – they encapsulate the name of the tab). In Criteria, I picked “Dropdown (from a range)”. Now it’s time for some magic. I used the formula: =’Form Responses 3′!$L$2:$R. Let’s break that down. The equal sign (=) denotes a formula. The ‘Form Responses 3’ (again, note the single quote marks), indicates the sheet to pull the information from. The bang (!) is crucial to using a different sheet. The dollar signs ($) go row by row. The L2:R is the range.

(* Quick side note. They had asked if this was a one time volunteer situation or recurring. However, they didn’t want to make the Form too complex, so it is a Start/End date question. I moved the “Start Date” to the end of the columns, then added six addtional colums. The Admin Asssistant will have to add dates if recurring into those columns.).

So, now the Print Date tab has drop-down menu of dates (from Form Responses) that will automatically update with dates.

Next is to pull the Volunteer information into the Print Date tab. I tried a FILTER function, but that only will test one column. That’s a problem since I want to check multiple columns (columns L-R).

After a bit too much overthinking, FILTER is the correct answer. There just needs to be multiple FILTER criteria involved.

In Cell D2; the following formula does what we need:

=FILTER(‘Form Responses 3’!F2:K,(‘Form Responses 3’!L2:L=A2)+(‘Form Responses 3’!M2:M=A2)+(‘Form Responses 3’!N2:N=A2)+(‘Form Responses 3’!O2:O=A2)+(‘Form Responses 3’!P2:P=A2)+(‘Form Responses 3’!Q2:Q=A2)+(‘Form Responses 3’!R2:R=A2))

Let’s break down that formula a bit. FILTER is the command. The FILTER function goes like this: =FILTER(range,condition1, [condition2,…]). We only want one condition. But, we want that one condition to apply to multiple columns.

Range: ‘Form Responses 3′(again, note the single quotes and their location) refers to another tab. The first part of this is pulling the information that we want copied (F2 through K – this equates to Teacher Name, Volunteer Last Name, Volunteer First Name, Awareness Training, Volunteer Registration, and Criminal Background).
Condition – The next bit is wrapped in paratheses since we are going to have multiples. We add a plus sign between each condition. You’ll note that the only thing that changes in each of these conditions is the column reference.

Google Spreadsheet tab with the following columns: Date,Time In ,Time Out,Classroom/ Teacher,Last Name,First Name,Volunteer Awareness Training Completed,Volunteer Registration Form Completed,Criminal Background Check Completed,Event,Volunteer Signature

I then turned Columns G, H, & I into checkboxes. (* Otherwise these will report as TRUE/FALSE since they are checkboxes on the other tab – Form Responses). This is simply selecting the columns (Command-click on the headers to deselect those) and choosing Insert | Checkbox.

Finally, I deleted most of the rows so that printing the tab Print Date will only print one page.

I advised the Admin Assist to turn on Notifications so that they know when Form has been completed. They can then add additional dates if needed (Columns M-R). They click the appropriate checkboxes for the required information:

  • Volunteer Awareness Training
  • Volunteer Registration Form
  • Criminal Background Check
The Google Spreadsheet the accepts Form Responses. Shows the heading columns (Timestamp,Time Volunteer is Coming...,Is this a one time occurrence?,Which days of the week will the volunteer be coming?,If this volunteer will come on a repeat basis, please indicate the end date of their volunteering.,Select your last name from the dropdown menu. ,Volunteer LAST Name,Volunteer FIRST Name,Volunteer Awareness Training Completed,Volunteer Registration Form Completed,Criminal Background Check Completed,Date Volunteer is Coming,Additional Dates).

Now the usage is pretty simple. In the morning, the Admin Assistant opens the Spreadsheet. That person clicks on the Date dropdown in Cell A2. If that date doesn’t appear, there are no volunteers. If the date does appear, they print out that sheet and post it. When the Volunteer arrives, they can sign in and add the time of arrival.