IF statement for a check box helper column
I have a form that has a status of sick or returning. There are other columns but not needed for the formula (other than name).
I want to add a helper checkbox and when someone submits a form for returning (they will have already submitted one for sick) the matching "sick" row and the new returning row will match the name and both will have the helper checkbox checked.
The point of this will trigger an automation to move any checked boxes in the helper column to an archive sheet.
Answers
-
Hi @rymurph
You could use a COUNTIF formula to see if that name appears more than once in the sheet, and if it does, check the box.
First we build the COUNTIF statement:
=COUNTIF([Employee Name]:[Employee Name], [Employee Name]@row)
Then we add the IF statement around it to look to see if it's in the sheet twice:
=IF(COUNTIF([Employee Name]:[Employee Name], [Employee Name]@row) > 1, 1, "")
Drag-fill this down your sheet but stop before the blank rows. As new forms are filled in, the formula will automatically populate in the next row (as long as the two cells above it contain the formula).
Here are some Help Center articles I used: COUNTIF Function / IF Statement / @row Function / Using > and <
Let me know if this works for you or if you have any questions!
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
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!