Invalid Argument Set in IF statement

Hi,

The formula below is aimed at returning the date of a process step based on the current process step. It returns an invalid argument set when the process step is 7) Released. I have no idea where to start to troubleshoot. Any ideas welcome!


=IFERROR(IF([Request Status]@row = "5) On Hold", "On Hold", IF([Request Status]@row = "7) Cancelled", "Cancelled", IF(AND(ISBLANK([Process Step]@row), [Request Status]@row = "1) New"), [Request Date]@row, IF(AND(ISBLANK([Process Step]@row), NOT([Request Status]@row = "1) New")), "Status Not Provided", IF(ISBLANK([Process Step]@row), "", IF(AND([Process Step]@row = "1) Being Purified", NOT(ISBLANK([Request Date]@row))), [Request Date]@row, IF(AND([Process Step]@row = "2) Awaiting CRO", NOT(ISBLANK([Request Purified Date]@row))), [Request Purified Date]@row, IF(AND([Process Step]@row = "3) In Config/Dev", NOT(ISBLANK([JIRA Creation Date]@row))), [JIRA Creation Date]@row, IF(AND([Process Step]@row = "4) Internal Testing", "", IF(AND([Process Step]@row = "5) Customer Testing", NOT(ISBLANK([JIRA Actual UAT Date]@row))), [JIRA Actual UAT Date]@row, IF(AND([Process Step]@row = "6) Awaiting Release", NOT(ISBLANK([Customer Sign Off Date]@row))), [Customer Sign Off Date]@row, IF(AND([Process Step]@row = "7) Released", NOT(ISBLANK([JIRA Actual PROD Date]@row))), [JIRA Actual PROD Date]@row, TODAY()))))))))))))), "Error with formula")

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @Anne-Solene Monrouzeau ,

    I don't remember. Sorry. I deconstructed it an put it back together. There was an issue with syntax someplace in the middle and a paren or 2 out of place.

    With complex formulas like this if you break each piece apart it makes identifying errors easier. I start by adding a hard return before each IF.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Try:

    =IFERROR(IF([Request Status]@row = "5) On Hold", "On Hold", IF([Request Status]@row = "7) Cancelled", "Cancelled", IF(AND(ISBLANK([Process Step]@row), [Request Status]@row = "1) New"), [Request Date]@row, IF(AND(ISBLANK([Process Step]@row), NOT([Request Status]@row = "1) New")), "Status Not Provided", IF(ISBLANK([Process Step]@row), "", IF(AND([Process Step]@row = "1) Being Purified", NOT(ISBLANK([Request Date]@row))), [Request Date]@row, IF(AND([Process Step]@row = "2) Awaiting CRO", NOT(ISBLANK([Request Purified Date]@row))), [Request Purified Date]@row, IF(AND([Process Step]@row = "3) In Config/Dev", NOT(ISBLANK([JIRA Creation Date]@row))), [JIRA Creation Date]@row, IF([Process Step]@row = "4) Internal Testing", "", IF(AND([Process Step]@row = "5) Customer Testing", NOT(ISBLANK([JIRA Actual UAT Date]@row))), [JIRA Actual UAT Date]@row, IF(AND([Process Step]@row = "6) Awaiting Release", NOT(ISBLANK([Customer Sign Off Date]@row))), [Customer Sign Off Date]@row, IF(AND([Process Step]@row = "7) Released", NOT(ISBLANK([JIRA Actual PROD Date]@row))), [JIRA Actual PROD Date]@row, TODAY()))))))))))), "Error with formula")

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Hi Mark,


    It seems to be working. Thanks!

    What did you change in the formula?

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @Anne-Solene Monrouzeau ,

    I don't remember. Sorry. I deconstructed it an put it back together. There was an issue with syntax someplace in the middle and a paren or 2 out of place.

    With complex formulas like this if you break each piece apart it makes identifying errors easier. I start by adding a hard return before each IF.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!