If And Formula question

Options

Hi,

I am giving myself a headache trying to resolve the issues I am having with this formula.

Currently this is set up to change the results in a dropdown Status column based on dates in other cells, its currently working well based on the above, as follows;

=IFERROR(IF([Actual Submit Date]@row = "", "Pending", (IF([Actual Compete date]@row = "", "Internal Review", (IF([Actual Date to Client]@row = "", "Send To Client", (IF([Actual Client comments]@row = "", "Client Review", (IF([Actual Piramal Response]@row = "", "Addressing Comments", (IF([Actual Final Client Approval]@row = "", "Client Approval", (IF([Actual submission to QA]@row = "", "QA Review", (IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved"))))))))))))))), "")


However, there is now another column which needs to be taken into account, not all documents require client approval, so we have a yes no drop down, "Client Review Required" - Yes/ No. If Yes, the above path can be followed, if no, then the status must jump straight to "QA Review" and continue from there.

I cannot get my head around how to lay that all out in one formula, all help is appreciated!

Tags:

Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Options

    Hi Kirsteen Leckie 

    Hope you doing well 

    Please try this formula -  =IFERROR(IF([Client Review Required]@row="Yes", IF([Actual Submit Date]@row = "", "Pending", (IF([Actual Compete date]@row = "", "Internal Review", (IF([Actual Date to Client]@row = "", "Send To Client", (IF([Actual Client comments]@row = "", "Client Review", (IF([Actual Piramal Response]@row = "", "Addressing Comments", (IF([Actual Final Client Approval]@row = "", "Client Approval", (IF([Actual submission to QA]@row = "", "QA Review", (IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved"))))))))))))))), ""),"") 

    I hope this is useful to you.  

     

    Have a Good Day! 

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Kirsteen Leckie
    Options

    Hi,

    Thank you for your help, this works well for the Yes option but unfortunately if No is selected the status does not change.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Give this a try:

    =IFERROR(IF([Client Review Required]@row="No", IF([Actual submission to QA]@row = "", "QA Review", IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved")), IF([Actual Submit Date]@row = "", "Pending", IF([Actual Compete date]@row = "", "Internal Review", IF([Actual Date to Client]@row = "", "Send To Client", IF([Actual Client comments]@row = "", "Client Review", IF([Actual Piramal Response]@row = "", "Addressing Comments", IF([Actual Final Client Approval]@row = "", "Client Approval", IF([Actual submission to QA]@row = "", "QA Review", IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved")))))))), "")

  • Kirsteen Leckie
    Options

    Hmmm, This is showing as Incorrect Argument Set. This one is 100% confusing my brain!

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Options

    Hi Kirsteen Leckie 

    Hope you doing well, 

    As you mentioned the formula worked for ‘Yes’ conditions. So, please let me know what output you want when the option is ‘No’ so I can provide you the updated formula with another condition in it. 

     

    Have a Good Day! 

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Kirsteen Leckie
    Options

    Hi,

    Thank you for your help so far, I need the output if no is selected to be 'QA Review' and then once the final date is input 'QA Approved' the final section of the formula is as follows;

    (IF([Actual submission to QA]@row = "", "QA Review", (IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved"))))))))))))))), ""),"")  - This is as it stands for is yes is selected. i need it to jump to here if No is selected.

    I hope that makes sense?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!