Linking a checkbox to a form return list

Lauren Acker

Hello,

I have a form that my employees fill out each month that returns to a sheet. I have another sheet that tracks whether each employee has returned a form for each month. I want the tracking sheet to automatically check the box in the appropriate month column when it registers that the employee's name appears on the submitted form list for that month. Up to this point I've been manually checking the boxes as forms are submitted. I know it should be simple but I can't seem to find the right formula. Pictures below.

Form return sheet (called December Quiz):

Tracking sheet (called Monthly Tracking):

Thank you

Genevieve P.

Hi@Lauren Acker

Try usingan IFERROR functionaround your whole formula, so that it returns a 0 instead of a "No Match", like this:

=IFERROR(IF(VLOOKUP(Employee@row, {December Form Return Sheet Range 1}, 1, 0) = Employee@row, 1), 0)

Let me know if this works for you!

Cheers,

Genevieve

Nick Brisson

Hi Lauren

I would add two columns to your Form Return Sheet.

One would be an Auto-Number/System column for Created (Date)

The second would be a formula to =Name@row+MONTH(CREATED DATE).

This would return "Lauren Acker12" for December for her row.

In your Tracking Sheet, I would create an IF statement that looks for the name of the person plus the month #. Below would be for December (month 12) and I'd set the formula for each column, changing the 12 to which ever month you needed.

=IF(VLOOKUP(([EMPLOYEE]@row + 12), {FORM RETURN SHEET Range 5}, 1, 0) = ([EMPLOYEE]@row + 12), 1)

This says If you find Lauren Acker12 on this form and the next form, then check the box.

After it works, I'd go back to your Form Return Sheet and hide those 2 columns added to make this work.

A little dirty, but heck it should work. I tested it on my end.

Best of luck!

Lauren Acker

Nick,

I played with the formula you suggested and managed to do it without adding columns since each form has its own form return per month. I will just have to link each month's column to its appropriate sheet. I got it to the following:

=IF(VLOOKUP(Employee@row, {December Form Return Sheet Range 1}, 1, 0) = Employee@row, 1)

This is returning checked boxes for names that it finds on the return sheet. However, for every name it doesn't find it is returning #NO MATCH instead of an unchecked box. Do you know of a way to fix this?

Thanks

Lauren Acker

Does anyone have a solution for the above #NO MATCH issue?

Genevieve P.

Hi@Lauren Acker

Try usingan IFERROR functionaround your whole formula, so that it returns a 0 instead of a "No Match", like this:

=IFERROR(IF(VLOOKUP(Employee@row, {December Form Return Sheet Range 1}, 1, 0) = Employee@row, 1), 0)

Let me know if this works for you!

Cheers,

Genevieve