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/usinglargewithduplicateresults
Best Answer

No problem! I'd be interested if it was the INVALID VALUE Blocking the formula, let me know what you find out.
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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!

No problem! I'd be interested if it was the INVALID VALUE Blocking the formula, let me know what you find out.
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 200 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!