Update on Formula Errors with Range References
We're investigating reports from a small number of customers who are reporting issues with cells containing a formula with a range reference. The issue is that the cell value flips between the correct calculated value and some formula error, such as #BLOCKED, #REF, etc. This behavior occurs when a Smartsheet internal service interacts with the sheet (i.e sheet automation, form submissions). This behavior is most obvious where column formulas are in use, as entire columns of cells can be impacted. However, it also occurs when column formulas are not being used. Reviewing cell history will reveal whether this undesired behavior has occurred.
To date, we’ve found 2 bugs (one is already fixed) that appear to have the same impact in cell history:
1. A bug was identified with cross-sheet formulas performing VLOOKUPs on a column range, trying to match an empty value. This issue has been fixed and should no longer occur.
2. A bug was identified where cross sheet reference names, which are incorrectly case sensitive, cause issues with formula calculations. We are working to fix this issue and will provide an ETA for release of this fix once changes are completed. Workaround in the interim: If column formulas are used in your scenario, try toggling to cell formula, saving, then toggling back to column formula. Our testing found that this would mitigate the case sensitivity issue.
Note: Both of these have been found to be impactful for solutions leveraging automation using cell changes as triggers, which the unexpected cell changes can generate many undesired notifications.
If you are being impacted by this issue and the suggested workaround is not helping, please contact Smartsheet Support so that our team can work with you to identify and fix the issue in the affected sheet(s).
Comments
-
Is there an ETA on resolving/upgrading cell link performance? These errors appear regularly and are really detrimental when they occur.
👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
- 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
- ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!
-
Hi Dan, by chance have you opened a case with our Support team? These symptoms are complex to investigate and I'd like to connect you with one of my Escalation Engineers to look into the specific symptom(s) you're experiencing.
-
I would also be keen to know an ETA for resolving bug 2 here. I've been talking with Smartsheet Support about it and it's causing major problems for our systems.
-
Hi Emma, this has already been resolved. Could you provide me with the case # you've been working with our Support team from, and I'll personally look into it.
