Update on Formula Errors with Range References

Isaac Benton
Isaac Benton Employee Admin
edited 11/01/22 in General Announcements

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).