If And Formula question

Kirsteen Leckie

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!

Kaveri Vipat

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! 

Kirsteen Leckie

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

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

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

Kaveri Vipat

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! 

Kirsteen Leckie

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?