Hi, I have a sheet that is keeping track of all employees that have been placed in a special assignment; the trick is that if an employee held the same assignment in the past, I need to have a box checked so we can look at their duty statement from that assignment. So, basically if the person is in my smartsheet (cell is named - applicant/candidate name) and has an active assignment cell named TA TYPE is not blank, I would like a box to be checked on that row. Any suggestions?

Thank you!


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @DHR Temp Assignment Team

    When you say the employee in your smartsheet - do you mean they match another list, that the [applicant/candidate name] field is not blank, or that the count of their name in the list is greater than one?

    I believe you're looking for an AND statement with the blank [TA TYPE]@row and the answer to my question in the first paragraph. If, for example, you're looking for non blank cell in both columns, the formula would look like this

    =IF(AND([applicant/candidate name]@row<>"", [TA TYPE]@row<>""), 1)

    Is this on track for what you need? If you can clarify the employee data, we can build the formula you need.

  • Hi Kelly, that worked perfectly! I now wonder if it is possible to write a code that if that box is checked AND the same applicant is selected (Eligibility Req Date) for the same classification (ie.., job code) a alert will be sent to the analyst to check a regulation).

    so John was hired and the check box has a check

    John applied again for the SAME job (this field is class code) and is being requested to work the same job again (field is eligibility) -

    an alert needs to be sent to the eligibility analyst to have them double check the regulation

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!