IFERROR Formula Help

Claire Graves
Claire Graves ✭✭
edited 06/01/23 in Formulas and Functions

Hi All -

I'm working through an IFERROR formula to keep the status column updated on one of my Smartsheets. My syntax is off, and I thought someone in the community might be able to idenitfy the issue(s) before I can. Would you take a look at the below and let me know how I can correct to overcome the dreaded #UNPARSEABLE?

Providing both the color version and the same thing in a format that's easy to copy paste.

Thanks!

=IFERROR(IF(AND([Applicant Still Interested?]@row = "Yes", [All Docs Received from Candidate?]@row = ""), "Pending Candidate Action", IF(AND([Date Offer Letter Sent]@row = <>"", [Date Offer Letter Returned]@row = ""), "Pending Candidate Action", IF(AND([Date of Drug Test]@row = <>"", [Drug Test Result]@row = ""), "Pending Candidate Action", IF(AND([Date of Background Check]@row = <>"", [Background Check Result]@row = ""), "Pending Candidate Action", IF(AND([Date HR Director Approved in NeoG]@row = <>"", [MSPB Approved in NeoGov]@row = ""), "Pending SPB Action", IF(AND([Date Entered in MAGIC EC]@row = <>"", [Date Response Received from SPB]@row = ""), "Pending SPB Action", IF(AND([Date Response Received from SPB]@row = <>"", [SPB Response]@row = "Approved"), "Approved by SPB", IF([Date Cancelled]@row = <>"", "Cancelled"))), "Pending HR Action"))))))

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Your closing parenthesis at the end are off. You should have 8 closing parenthesis to close out all IFs then comma then "output if error" then finally a single closing parenthesis.

    =IFERROR(IF(AND([Applicant Still Interested?]@row = "Yes", [All Docs Received from Candidate?]@row = ""), "Pending Candidate Action", IF(AND([Date Offer Letter Sent]@row = <>"", [Date Offer Letter Returned]@row = ""), "Pending Candidate Action", IF(AND([Date of Drug Test]@row = <>"", [Drug Test Result]@row = ""), "Pending Candidate Action", IF(AND([Date of Background Check]@row = <>"", [Background Check Result]@row = ""), "Pending Candidate Action", IF(AND([Date HR Director Approved in NeoG]@row = <>"", [MSPB Approved in NeoGov]@row = ""), "Pending SPB Action", IF(AND([Date Entered in MAGIC EC]@row = <>"", [Date Response Received from SPB]@row = ""), "Pending SPB Action", IF(AND([Date Response Received from SPB]@row = <>"", [SPB Response]@row = "Approved"), "Approved by SPB", IF([Date Cancelled]@row = <>"", "Cancelled")))))))), "Pending HR Action")

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Your closing parenthesis at the end are off. You should have 8 closing parenthesis to close out all IFs then comma then "output if error" then finally a single closing parenthesis.

    =IFERROR(IF(AND([Applicant Still Interested?]@row = "Yes", [All Docs Received from Candidate?]@row = ""), "Pending Candidate Action", IF(AND([Date Offer Letter Sent]@row = <>"", [Date Offer Letter Returned]@row = ""), "Pending Candidate Action", IF(AND([Date of Drug Test]@row = <>"", [Drug Test Result]@row = ""), "Pending Candidate Action", IF(AND([Date of Background Check]@row = <>"", [Background Check Result]@row = ""), "Pending Candidate Action", IF(AND([Date HR Director Approved in NeoG]@row = <>"", [MSPB Approved in NeoGov]@row = ""), "Pending SPB Action", IF(AND([Date Entered in MAGIC EC]@row = <>"", [Date Response Received from SPB]@row = ""), "Pending SPB Action", IF(AND([Date Response Received from SPB]@row = <>"", [SPB Response]@row = "Approved"), "Approved by SPB", IF([Date Cancelled]@row = <>"", "Cancelled")))))))), "Pending HR Action")

  • That definitely helped, Paul, thanks! It is now "PARSEABLE", but not working properly. More fiddling needed. I'm noticing that for some reason "IF(AND([Date HR Director Approved in NeoG]@row = <>""," is operating as a "value if false" for one of the IF statements. That's the only instance where I've got anything showing up as a "value if false." Not sure why!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Based on the syntax it doesn't look like it would be operating that way. Are you able to share some screenshots?

  • Here's what I'm seeing. I've drawn a red line under the IF where my cursor is at the time of the screen shot. That's the portion that is showing up as the value if false.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    That is to be expected if you are referring to the little formula helper box indication. That should be the case for every IF except the first one because the next IF is the "value if false" for the one before it.


    Is it outputting the correct values?

  • Ah, I see! The only two values that it's generating are "Pending Candidate Action" and "Pending HR Action." So, the very first option and the very last option.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It could actually be one of the first 4 IFs.


    Depending on how your sheet is filled out, you may need to adjust the order of the IFs. The way to think about it is that it will work from left to right and stop on the first true value.


    So if the first one is true, anything you do in the sheet after that will not be registered. For example... If you are part way through filling things out on the row and are getting a true value for the third IF but then decide to cancel, you will not get a "Cancelled" status even if you enter a [Cancelled Date] because the third IF is already stopping at true.


    You are most likely going to want to move the "Cancelled" IF to the beginning so that it essentially overrides all other options regardless of when the date is entered and then start from the end of your process and think backwards.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!