It's Only Blocked Until I Kick It

I have a formula that leverages some sheet summary formulas and returns a true/false to support a report filter. When I change data in the sheet the sheet summary formulas sometimes return a #BLOCKED, but it is not consistent. When it does, if I go into the formula and edit it (without actually changing anything), then the error clears and everything returns to normal. Screenshot:

The [2nd Largest Total Score] formula is =LARGE([Total Score]:[Total Score], [Count of Largest]# +1). If I clear the "+1", it corrects. Then I can add the +1 back in, and it's happy.

Any ideas? Thanks in advance. BTW, all of this is meant as an alternative to solve https://community.smartsheet.com/discussion/72045/using-large-with-duplicate-results

Best Answer

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Scott Peters

    I can't replicate the error you're seeing, so I wonder if it may be related to the other formulas in your sheet. How is the Total Score being created?

    I presume the "Count of Largest" is:

    =COUNTIF([Total Score]:[Total Score], [Largest Total Score]#)


    Can you try adding a VALUE function around your [Count of Largest]# in the current formula, like so:

    =LARGE([Total Score]:[Total Score], VALUE([Count of Largest]#) +1)


    Can you also try adding in a new field as a test field to just have =[Count of Largest]# in case this is the field causing the errors? If this also gives you BLOCKED it will help us narrow down the culprit.

    Thanks!

    Genevieve

  • Scott Peters
    Scott Peters ✭✭✭✭✭✭

    Hi @Genevieve P. - This evening I can't seem to replicate the error, either. However, while trying to retrace my steps I did find some errors came when there was only one or two total scores. The 2nd and 3rd largest total score counts produced some #INVALID VALUE. I was able to solve those using an if(iserror(myformula), 0, myformula). I will work with this over the next few days and reach back if I can reproduce the problem. I am also happy to share a copy of the sheet with you if you'd like to tinker with it. Thanks for your help!

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Scott Peters

    No problem! I'd be interested if it was the INVALID VALUE Blocking the formula, let me know what you find out.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!