IFERROR and ISERROR do not work properly!

#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.

Sign in to join the conversation!

Trending discussions

Auto Fill Date When Column Checked
I want my date column to auto fill when I check the column Verification and Closure. What formula do I use? I want to ensure that the date doesn't change as time goes on.
Employee Reviews - Pulling & Averaging Category Scores
Hi all, thanks in advance for your assistance! I am working on building a new employee review process for my company and am caught up on one of the final steps. Here's where I am at: I have created an employee review form that individual managers will complete Each employee will have 3-5 reviewers The form submissions will…
Need Formula for Subtracting Order Quantities from Inventory Quantities
Hi! What formula would I use in Smartsheet to automatically subtract a quantity from one sheet based on what is submitted in another sheet? I have one sheet that is where we will manage the "orders submitted" and on that sheet is different columns for different items that can be ordered and the quantities requested for…