Linking a checkbox to a form return list
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
Best Answer
-
Try using an IFERROR function around 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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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!
-
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
-
Does anyone have a solution for the above #NO MATCH issue?
-
Try using an IFERROR function around 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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 422 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!