Formula not refreshing when date changes - Can't tell I'm connected?
Hello ~ I'm new to Smartsheet and would appreciate some assistance. I have a formula that changes the color priority as the next action due approaches or passes, reflecting if the task is pending action from others: Rows that are marked "Done" then gets conditionally formatted to Grey (this was a workaround to not having 5 colors on a RGYB symbol.)
=IF(Status26 = "Completed", "Done", IF(AND(Status26 = "Pending Others", [Due Next]26 <> TODAY()), "Blue", IF(OR([Due Next]26 = TODAY(), [Due Next]26 < TODAY()), "Red", IF(([Due Next]26 - TODAY()) < 3, "Yellow", "Green"))))
Generally, it works great - except occasionally, I seem to be logged out of Smartsheet but I can't tell until I get an error from the Outlook plug in that won't (seemingly) update the Sheet. The only way I can tell is when the date becomes TODAY(), if something was Blue, it's staying Blue instead of turning Red - the formula is not refreshing the value with the date advance. If I change the Due Next date to something later and then re-enter it with the original value of today's date (pinging the server), it goes Red correctly. Resaving the Sheet doesn't trigger it. It seems like I'm logged in (I just leave the Sheet open all the time), but Outlook won't connect. If I close and reopen the sheet, multiple duplicate entries will show up on the Sheet and the formulas are all then updated.
Is there a way to tell if the Sheet is not connecting? to make sure the formula auto-refreshes and I know the values are staying current? (When the error threw, I reopened the Sheet from scratch, and all was updated.)
Thank you in advance for any feedback!
Help Article Resources
Check out the Formula Handbook template!