Trouble with IF AND formula - i need some help :o(

paul112233

Hi everyone, need some help, I need a formula to display 5 different outputs based on the conditions set out below, I have tried this based on a IF(AND formula and not getting much back, I can get 4 and 5 to work using

=IF(Cancelled@row = true, "5. Request Cancelled", IF([Upload to IMS]@row = true, "4. Request Complete"))

However when trying the 3rd element it all goes wrong, any assistance would be greatly appreciated.

Here are the 5 elements I need from my formula:

1.      Document proofing =

IF [Created Date] has a value and [Approver 1 Approval] is blank [Approver 2 Approval] is blank and [Upload to IMS] is Blank and [Cancelled] is blank then display “1. Document Proofing”

2.      Document Awaiting Approval =

IF [Created Date] has a value and [Approver 1 Approval] is “Submitted” or  [Approver 2 Approval] is “submitted” and [Upload to IMS] is Blank and [Cancelled] is blank then display “2. Document Awaiting Approval”

3.      Awaiting Upload to IMS =

IF [Created Date] has a value and  [Approver 2 Approval] is “Approved” and [Upload to IMS] is Blank and [Cancelled] is blank then display “3. Awaiting Upload to IMS”

4.      Request Complete =

IF [Created Date] has a value and [Upload to IMS] is true and [Cancelled] is blank then display “4. Request Complete”

5.      Request Cancelled =

IF [Created Date] has a value and [Cancelled] is true then display “5. Request Cancelled”

[Created Date] is a date field

[Approver 1 Approval] and [Approver 2 Approval]  are text fields

[Upload to IMS] is a check box field

[Cancelled] is a Check box field

paul112233

Hi Brent

Thank you so much for this, this is similar to what i was trying so i'm going to study what i was doing wrong and hopefully i'll finally figure out this formula business. :o)

Brent Wilson

I Took out some of the logic which may streamline it or may cause you problems..

Ex: I would hope you do not delete the created date after the document has been approved. I don't check for that and as a result, if the creation date is empty but the document has been approved it will show as such.

=IF([CANCELLED]@row=1, "5. Request Cancelled", IF([Upload to IMS]@row=1, "4. Request Complete", IF([Approver 2 Approval]@row="Approved", "3. Awaiting Upload to IMS", IF(OR([Approver 1 Approval]@row="Submitted", [Approver 2 Approval]@row="Submitted"), "2. Document Awaiting Approval", IF([Created Date]@row = "", "", "1. Document Proofing")))))

If you want that check in... Use this one

=IF([CANCELLED]@row=1, "5. Request Cancelled", IF(AND(NOT([Created Date]@row = ""),[Upload to IMS]@row=1), "4. Request Complete", IF(AND(NOT([Created Date]@row = ""),[Approver 2 Approval]@row="Approved"), "3. Awaiting Upload to IMS", IF(AND(OR([Approver 1 Approval]@row="Submitted", [Approver 2 Approval]@row="Submitted"),NOT([Created Date]@row = "")), "2. Document Awaiting Approval", IF([Created Date]@row = "", "", "1. Document Proofing")))))

paul112233

Cheers for this, I'll give them a go on Monday

paul112233

Hi Brent

Thank you so much for this, this is similar to what i was trying so i'm going to study what i was doing wrong and hopefully i'll finally figure out this formula business. :o)