IFERROR Formula Help

Claire Graves

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"))))))

Paul Newcome

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")

Paul Newcome

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")

Claire Graves

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

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

Claire Graves

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

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?

Claire Graves

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

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.