If formula with several criteria

Kirsteen Leckie

Hi,

I was working on a formula last week, but I am still struggling with a second criteria on this.

the way its set up, updating the field depending on dates being input into others. this works well as follows;

=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"))))))))))))))), ""), "")

However this is only if the Client review required cell is 'Yes' I am trying to resolve when the cell is 'No' - where 'No' is selected the status must miss the middle Status sections and jump right to 'QA Review and then 'Approved' As per this latter section - "(IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved"))"

I am really struggling on how I get this section into the above formula - or if this is even possible?

Amit Wadhwani

@Kirsteen Leckie

I made some tweaks to the formula, please try below

=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"))))))))))))))), IF([Client review Required]@row = "No", IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved")))

Amit Wadhwani

Hi @Kirsteen Leckie

You may want to try below variation

=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"))))))))))))))), "",IF([Client review Required]@row = "No",IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved"))),"")

Kirsteen Leckie

Hi @Amit Wadhwani,

Thank you for your help, however this is generating a #INCORRECT ARGUMENT SET error. apologies, I am really struggling with getting my head around this one!

Hollie Green

If the Client review required will always be filled in with either a yes or a no you can put it where the false value would go for the Client review required = "yes" would be in your formula.

When I have an extremely long formula like that I usually click after each , to find where it tells me my first value if false goes.

Amit Wadhwani

@Kirsteen Leckie

I made some tweaks to the formula, please try below

=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"))))))))))))))), IF([Client review Required]@row = "No", IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved")))

Kirsteen Leckie

@Amit Wadhwani Thank you!