Help with combining a formula.. is it possible?

Harper141

I have 3 columns of data:

1. Required Fields Status column - formula that looks at specified columns and returns YES if all columns are complete, NO if not
2. Financial $ Amount Process column - formula that looks at $ amount value in a column and returns a value depending on the range of the $ amount
3. Approvals Complete column - formula that looks at specified columns and returns YES if all columns are complete

The value in each of these 3 columns determines the next step in the process for the end user. I'd like to bring these all together in a single column to provide direction on next steps.

I tested each of these statements below individually and they work – but I’d like to combine them into a single long formula and I feel like that should be possible with the right, really complex formula (but maybe it’s just not?).

 

=IF([Required Fields Status]@row = "NO", "Request Incomplete - please complete all required fields & save")

 

=IF(AND([Required Fields Status]@row = "YES", [Final $ Amount Process]@row = "No Financial Impact"), "Ready to Close - No Financial Impact")

 

=IF(AND([Required Fields Status]@row = "YES", [Final $ Amount Process]@row = "No Approval Required"), "Ready to Submit to Billing")

 

=IF(AND([Required Fields Status]@row = "YES", [Final $ Amount Process]@row = "Approval Required"), "Ready to Request Approvals")

 

=IF(AND([Required Fields Status]@row = "YES", [Final $ Amount Process]@row = "Approval Required", [Approvals Complete]@row = "Complete"), "Ready to Update Client Outcome & Submit to Billing")

 

So I tried:

=IF (OR ([Required Fields Status]@row = "NO", "Request Incomplete - please complete all required fields & save"), (AND([Required Fields Status]@row = "YES", [Final $ Amount Process]@row = "No Financial Impact"), "Ready to Close - No Financial Impact"), (AND([Required Fields Status]@row = "YES", [Final $ Amount Process]@row = "No Approval Required"), "Ready to Submit to Billing"), (AND([Required Fields Status]@row = "YES", [Final $ Amount Process]@row = "Approval Required"), "Ready to Request Approvals"), (AND([Required Fields Status]@row = "YES", [Final $ Amount Process]@row = "Approval Required", [Approvals Complete]@row = "Complete"), "Ready to Update Client Outcome & Submit to Billing") )

 

Obviously, that was #UNPARSEABLE. Am I trying to do the impossible?

Thanks in advance for any help!!

Samuel Mueller

@Harper141 try this formula

=IF([Required Fields Status]@row = "NO", "Request Incomplete - please complete all required fields & save",

IF([Final $ Amount Process]@row = "No Financial Impact", "Ready to Close - No Financial Impact",

IF([Final $ Amount Process]@row = "No Approval Required", "Ready to Submit to Billing",

IF([Final $ Amount Process]@row = "Approval Required", "Ready to Request Approvals", 

IF(AND([Final $ Amount Process]@row = "Approval Required", [Approvals Complete]@row = "Complete"), "Ready to Update Client Outcome & Submit to Billing", "")))))

 

Samuel Mueller

@Harper141 try this formula

=IF([Required Fields Status]@row = "NO", "Request Incomplete - please complete all required fields & save",

IF([Final $ Amount Process]@row = "No Financial Impact", "Ready to Close - No Financial Impact",

IF([Final $ Amount Process]@row = "No Approval Required", "Ready to Submit to Billing",

IF([Final $ Amount Process]@row = "Approval Required", "Ready to Request Approvals", 

IF(AND([Final $ Amount Process]@row = "Approval Required", [Approvals Complete]@row = "Complete"), "Ready to Update Client Outcome & Submit to Billing", "")))))

 

Harper141

YASSSS that did it!!!! THANK YOU!!

Samuel Mueller

Happy to help 😁