IFERROR and ISERROR do not work properly!

Alexander_Hritosv

No matter if I use IFERROR or ISERROR formula Smartsheet always returns #BLOCKED

Some how the back end ignores the IF/ISERROR formals and is returning  #BLOCKED 

 

=IF(ISERROR([Project name]1293), " - ", "Green")

result is not  -  but  #BLOCKED

=IFERROR([Project name]1293, " - ")

result is not  -  but  #BLOCKED 

 

Alexander_Hritosv

Is any one having the same problem?

Paul Newcome

It is returning blocked because the cell it is referencing is an error and will therefore not even try to run the ERROR formula. 

 

#BLOCKED means it is not even trying to run the formula because of an issue elsewhere.

 

You need to reference the actual formula instead of the cell you have it in.

Alexander_Hritosv

#BLOCKED is just a type of error, and as far as I am aware the idea of IFERROR formula is just that - look for errors and if any to replace the error message with something else ( no matter what kind of error is occurred https://help.smartsheet.com/articles/2476176-formula-error-messages )

That is how I am using it in Excel. 

Is it somehow different here? (looks like the same idea to me https://help.smartsheet.com/function/iferror )

 

J. Craig Williams

"#BLOCKED is just a type of error" is true.

But the behavior of the #BLOCKED is a bit different. It seems to check for non-error values BEFORE it checks if the formula works or the references are valid.

I believe there are some oddities in Smartsheet's parsing routines that are not optimized and I haven't bothered to spend a lot of time figuring out their parsing order. But they sometimes seem odd -- like #BLOCKED.

Here's another one:

In a Contact List type column, try

=IF("Bob is here")

and the result is 

#INCORRECT ARGUMENT SET

=IF("No he's not", 1)

the result is 

#INVALID DATA TYPE

but both of those should have been preempted by 

#CONTACT COLUMN DOES NOT SUPPORT FORMULAS

which would be what my code would return -- if the first character is "=" -- then stop parsing. 

#BLOCKED seems to do some sort of check on the inputs before it parses them. Sometimes an error is masked until it reaches the error, sometimes not.

This won't throw an error, but it has one:

=IF(true, 1, IF(true, 1, 1 / 0))

Sorry the length. I'd suggest writing an enhancement request.

Craig

 

 

Paul Newcome

Is it somehow different here?

 

It is different. In Smartsheet, you will automatically get #BLOCKED if a cell being reference has an error in it. If you referred to the formula itself in the IF/ISERROR statement, then you would get the results you're looking for.

 

#BLOCKED is just a type of error

 

I would say yes and no. While it is an error, it seems to be treated more like a notice of sorts by Smartsheet. It isn't so much telling you that there is an error in your formula itself, but that there is an issue elsewhere on the page affecting your formula that needs addressed. 

 

Should the IF/ISERROR functions pick up on #BLOCKED as an error? I think so, but unfortunately it doesn't. Just one of the many differences between this and Excel.