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
-
No problem! I'd be interested if it was the INVALID VALUE Blocking the formula, let me know what you find out.
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!