If Formula

Samuel Dowdy Jr.

I need help understanding why I keep getting #INVALID OPERATION with this formula. It only happens when I want to look at Audit Status. I know its a large formula but Im using it to check for missing items and notify the team on next steps.

=IF( OR(ISBLANK([Profit Center]@row ), [Profit Center]@row = "⚠️ PROFIT CENTER NOT AVAILABLE"), "Profit Center Not Available",IF( [Audit Status]@row <> "Closed", "Audit Required",IF( AND(NOT(ISBLANK([Buyout?]@row )), [PO Status]@row = "Needs Review"), "Buyout",IF( AND( NOT(ISBLANK([Scheduled Delivery Date]@row )), [Delivery Status]@row <> "Received", [Scheduled Delivery Date]@row > TODAY(), [PO Status]@row = "Approved" ), "Scheduled for Delivery",IF( OR([PO Status]@row = "Not Approved", [PO Status]@row = "Canceled"), "All Requirements Met",IF( AND(ISBLANK([Quote Amount $ - Approve by PM]@row ), [PO Status]@row = "Approved"), "Quote is Required",IF( ISBLANK([Vendor Company]@row ), "Vendor Company Missing",IF( ISBLANK([Vendor Contact]@row ), "Vendor Contact Missing",IF( AND([PO Status]@row <> "Approved", [PO Status]@row <> "Multiple Invoice to Existing PO"), "PO Not Approved",IF( ISBLANK([Purchased By]@row ), "Purchased By Missing",IF( [Delivery Status]@row = "Back Order", "Back Order - Delivery Status",IF( [Delivery Status]@row = "Rejected", "Rejected - Delivery Status",IF( [Delivery Status]@row = "Missing Items", "Missing Items - Delivery Status",IF( [Delivery Status]@row <> "Received", "Delivery Not Received",IF( ISBLANK([Received by]@row ), "Received By Missing",IF( [Invoice Review Status]@row = "Waiting on Invoice", "Waiting on Invoice",IF( [Invoice Review Status]@row = "Needs Review", "Invoice Review Required",IF( ISBLANK([Posted Date]@row ), "Posted Date Missing",IF( AND([Payment Status]@row <> "Paid", [Payment Status]@row <> "Cancelled – Payment canceled due to changes in requirements or agreement."), "Payment Required",IF( AND([Payment Status]@row = "Paid", ISBLANK([Date Paid]@row )), "Date Paid Required",IF( AND([Payment Status]@row = "Paid", ISBLANK([Amount Paid]@row )), "Amount Paid Required",IF( ISBLANK([Invoice Date]@row ), "Invoice Date Missing",IF( [Vendor Waiver Status]@row <> "Waiver Complete", "Waiver Completion Required",IF( AND([Lien Status]@row <> "Lien Released", [Lien Status]@row <> "No Lien"), "Lien Release Required",IF( AND(ISBLANK([Amount Paid]@row ), [Payment Status]@row <> "Cancelled – Payment canceled due to changes in requirements or agreement."), "Amount Paid Missing",IF( AND(ISBLANK([Date Paid]@row ), [Payment Status]@row <> "Cancelled – Payment canceled due to changes in requirements or agreement."), "Date Paid Missing",IF( OR(ISBLANK([Payment Type]@row ), [Payment Type]@row = "Purchase Order (PO)"), "Payment Type Required",IF( [Audit Late]@row = 1, "Audit Due Past",IF( [Anticipated Payment Date]@row < TODAY(), "Anticipated Payment Date Past", "All Requirements Met"))))))))))))))))))))))))))))

Kelly Moore

Hey @Samuel Dowdy Jr.

I tested the formula below and it does not produce errors. However, the formula logic given in your formula will not yield the responses you are expecting. You will need to play with the order of the IF statements, and probably add more criteria to some of your later IFs to produce the desired responses. Recall that the first 'true' response will produce a response.

=IF(OR(ISBLANK([Profit Center]@row ), [Profit Center]@row = "⚠️ PROFIT CENTER NOT AVAILABLE"), "Profit Center Not Available", IF([Audit Status]@row <> "Closed", "Audit Required", IF(AND(NOT(ISBLANK([Buyout?]@row )), [PO Status]@row = "Needs Review"), "Buyout", IF(AND(NOT(ISBLANK([Scheduled Delivery Date]@row )), [Delivery Status]@row <> "Received", [Scheduled Delivery Date]@row > TODAY(), [PO Status]@row = "Approved"), "Scheduled for Delivery", IF(OR([PO Status]@row = "Not Approved", [PO Status]@row = "Canceled"), "All Requirements Met", IF(AND(ISBLANK([Quote Amount $ - Approve by PM]@row ), [PO Status]@row = "Approved"), "Quote is Required", IF(ISBLANK([Vendor Company]@row ), "Vendor Company Missing", IF(ISBLANK([Vendor Contact]@row ), "Vendor Contact Missing", IF(AND([PO Status]@row <> "Approved", [PO Status]@row <> "Multiple Invoice to Existing PO"), "PO Not Approved", IF(ISBLANK([Purchased By]@row ), "Purchased By Missing", IF([Delivery Status]@row = "Back Order", "Back Order - Delivery Status", IF([Delivery Status]@row = "Rejected", "Rejected - Delivery Status", IF([Delivery Status]@row = "Missing Items", "Missing Items - Delivery Status", IF([Delivery Status]@row <> "Received", "Delivery Not Received", IF(ISBLANK([Received by]@row ), "Received By Missing", IF([Invoice Review Status]@row = "Waiting on Invoice", "Waiting on Invoice", IF([Invoice Review Status]@row = "Needs Review", "Invoice Review Required", IF(ISBLANK([Posted Date]@row ), "Posted Date Missing", IF(AND([Payment Status]@row <> "Paid", [Payment Status]@row <> "Cancelled – Payment canceled due to changes in requirements or agreement."), "Payment Required", IF(AND([Payment Status]@row = "Paid", ISBLANK([Date Paid]@row )), "Date Paid Required", IF(AND([Payment Status]@row = "Paid", ISBLANK([Amount Paid]@row )), "Amount Paid Required", IF(ISBLANK([Invoice Date]@row ), "Invoice Date Missing", IF([Vendor Waiver Status]@row <> "Waiver Complete", "Waiver Completion Required", IF(AND([Lien Status]@row <> "Lien Released", [Lien Status]@row <> "No Lien"), "Lien Release Required", IF(AND(ISBLANK([Amount Paid]@row ), [Payment Status]@row <> "Cancelled – Payment canceled due to changes in requirements or agreement."), "Amount Paid Missing", IF(AND(ISBLANK([Date Paid]@row ), [Payment Status]@row <> "Cancelled – Payment canceled due to changes in requirements or agreement."), "Date Paid Missing", IF(OR(ISBLANK([Payment Type]@row ), [Payment Type]@row = "Purchase Order (PO)"), "Payment Type Required", IF([Audit Late]@row = 1, "Audit Due Past", IF([Anticipated Payment Date]@row < TODAY(), "Anticipated Payment Date Past", "All Requirements Met")))))))))))))))))))))))))))))

Shout out if you need more help

Kelly