How do i get a cell to return a TEXT value based on several IF criteria from other cells?

Hi

I have an audit planner which i am trying to automate some what, i would like the status to change based on a set criteria.

I.e. AUDIT STATUS = "Awaiting Scheduling" IF IMS AUDIT is CHECKED and SCHEDULED DATE is BLANK

AUDIT STATUS = "Scheduled" IF IMS AUDIT is CHECKED and SCHEDULED DATE is NOT BLANK and COMPPETED DATE is BLANK

AUDIT STATUS = "Overdue' IF IMS AUDIT is CHECKED and SCHEDULED DATE is NOT BLANK and COMPETED DATE is BLANK and PROPOSED AUDIT DATE is less than TODAY.

AUDIT STATUS = "Completed" IF IMS AUDIT is CHECKED and SCHEDULED DATE is NOT BLANK and COMPLETED DATE is NOT BLANK

Any help would be greatly appreciated



Best Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    Answer ✓

    This ought to do it:

    =IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row = ""), "Awaiting Scheduling", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = "", [IMS Audit Proposed Audit Month]@row < TODAY()), "Overdue", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = ""), "Scheduled", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row <> ""), "Completed", ""))))

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • Parker Oxford
    Parker Oxford ✭✭✭✭✭
    Answer ✓

    See formula below, it should work if you copy and paste into your column so let me know if anything doesn't work.

    If there are any issues, ensure I got all the column names correct.

    =IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row=""),"Awaiting Scheduling",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row=""),"Scheduled",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row="",[IMS Audit Proposed Audit Month]@row<TODAY()),"Overdue",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row<>"", "Completed",""))))
    


Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    Answer ✓

    This ought to do it:

    =IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row = ""), "Awaiting Scheduling", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = "", [IMS Audit Proposed Audit Month]@row < TODAY()), "Overdue", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = ""), "Scheduled", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row <> ""), "Completed", ""))))

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • Parker Oxford
    Parker Oxford ✭✭✭✭✭
    Answer ✓

    See formula below, it should work if you copy and paste into your column so let me know if anything doesn't work.

    If there are any issues, ensure I got all the column names correct.

    =IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row=""),"Awaiting Scheduling",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row=""),"Scheduled",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row="",[IMS Audit Proposed Audit Month]@row<TODAY()),"Overdue",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row<>"", "Completed",""))))
    


  • paul112233
    paul112233 ✭✭✭

    Great, many thanks guys, most appreciated.😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!