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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!