Checkbox Column - Column Formula

Current Statuses = "Complete", "Incomplete", "Pending" based on below formula:

=IF(NOT(ISBLANK([Recovery Collected Date]@row)), "Complete", IF(AND(ISBLANK([Recovery Collected Date]@row), ISBLANK([Anticipated Collection Date]@row)), "Incomplete", IF(AND(NOT(ISBLANK([Anticipated Collection Date]@row)), ISBLANK([Recovery Collected Date]@row)), "Pending", "")))

I would also like to add the status, "N/A" which will be based on a checkbox column. How can this be added to my current formula?

Best Answer

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓

    Create a column called NA and add this to the beginning of your formula and hit enter, Smartsheet will add the closing parentheses:

    IF(NA@row = true, "N/A",

    So:

    =IF(NA@row = true, "N/A", IF(NOT(ISBLANK([Recovery Collected Date]@row)), "Complete", IF(AND(ISBLANK([Recovery Collected Date]@row), ISBLANK([Anticipated Collection Date]@row)), "Incomplete", IF(AND(NOT(ISBLANK([Anticipated Collection Date]@row)), ISBLANK([Recovery Collected Date]@row)), "Pending", ""))))

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓

    Create a column called NA and add this to the beginning of your formula and hit enter, Smartsheet will add the closing parentheses:

    IF(NA@row = true, "N/A",

    So:

    =IF(NA@row = true, "N/A", IF(NOT(ISBLANK([Recovery Collected Date]@row)), "Complete", IF(AND(ISBLANK([Recovery Collected Date]@row), ISBLANK([Anticipated Collection Date]@row)), "Incomplete", IF(AND(NOT(ISBLANK([Anticipated Collection Date]@row)), ISBLANK([Recovery Collected Date]@row)), "Pending", ""))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!