How to Mask Circular Reference and Blocked Errors?

Justin Gibbemeyer

I have a SmartSheet that I have that references a Cell that calculates employee capacity. I created two cells that reference it, one is a point value, the other is a cell that calculates the percentage of the Employee Capacity cell.

Employee Capacity -------------------> Action / Task Hours <---------------> Percentage of Employee Capacity

The above are an example of the Cells.

So the idea is that a user can entire either a specific Task hour amount and the Percentage cell will calculate the percentage of the employees total capacity, OR they can enter a specific Percentage in, and the Task Hours will automatically calculate.

For example, if an employee has 100 hours of capacity, and they enter in a task that takes 3 hours, I want it to calculate in the third field it is 3% of total employee capacity, or vice versa, if they enter in 3% in the Percentage field, the Task Hour will automatically calculate and show 3 hours.

While the circular reference was intentional to allow users the freedom to enter either or information, I have a cell that adds up all the entries that will not calculate due to unused fields showing a circular reerence error and the sum field cant compute this.

Is there a way to mask the error so the cell remains blank until someone enters data into one of the two fields?

Thanks!

Andrée Starå

Hi @Gibby913

I hope you're well and safe!

If I understand your questions correctly, I think that you can use the IFERROR function.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Justin Gibbemeyer

Hi Andree, thanks for your help. So I have tried that and a few different variations with no luck. Can you help me with the formula?

This is the last cell formula I tried using based off some research I did on this:

=IF(ISERROR([Sprint Hours Total Capacity]21 * [Sprint Hours]19), "Test")

John Pudar

Hi @Justin Gibbemeyer,

If you take your previously functioning formula that was yielding the error and replace it with X below, you should get the outcome you were looking for:

=IFERROR(X, "")

Paul Newcome

I would suggest inserting two more columns. This way you have a set for users to enter their data into and the other set will be strictly for calculations/display. This will help avoid the circular reference error.

Justin Gibbemeyer

https://community.smartsheet.com/discussion/comment/300770#Comment_300770

Hi @John Pudar

I just tried that and now it says #Blocked. Thoughts?

Thanks!

Paul Newcome

Unfortunately both of those errors are errors that cannot be "masked" using IFERROR or ISERROR functions. You will have to restructure your sheet to avoid both of those errors entirely.