How to stop Alerts being triggered by TODAY() formula
Hi Everyone,
This is my first post on here and hope someone can point me in the right direction.
I have a formula cell which references TODAY() in a certain IF criteria. I also have several alerts going to various users which are generated if cells are added or changed on specific rows (including an alert to myself to show any changes made to the sheet the previous day).
Once a user has "touched" the sheet it will automatically update the TODAY() formula cells generating an alert as per the rules set.
Is there a way to stop alert triggers from looking at that one field without having to add criteria for all other fields on the sheet (which I can't do as I have too many other fields) or a way to prevent alerts from referencing cells with a formula?
Any help would be greatly appreciated.
Thanks
Comments
-
Hi Peter,
What type is the column with the IF formula?
Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andrée,
The column is a Text/Number format.
The formula is calculating as follows: =IF([Open or Closed]1 = "Open", TODAY() - [Created]1, [Date Closed]1 - [Created]1)
If the row is showing as "Open" then the formula will calculate the difference from TODAY() to the date the row was created.
The formula is working well, it's just the alerts I need to solve.
Let me know what you think.
Peter
-
You can specify fields by using the "+ Specify a change" option. See screenshot below.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Did you get it working?
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andrée,
No I didn't get it working. I have too many fields that could be edited to select them all in the suggestion from Paul.
Will have to find a more manual way forward.
Peter
-
Ok. A little unorthodox, but it may just work (not tested yet). Insert one more column. use a JOIN function to pull ALL columns EXCEPT for the one that has the TODAY function in it. Use that JOIN column to trigger the alert. The JOIN column will change whenever something in the other columns changes except for the one column you excluded (the TODAY column).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Paul,
Great workaround! (if it works)
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Let us know if it works with Pauls workaround!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks! I had literally just closed out of this thread when the idea hit me. I have absolutely NO idea where the thought came from. I'm getting ready to test it to see if I can make it work. I'll keep everyone posted...
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It actually worked!! Haha.
When building the alert, I set it to trigger when the JOIN column changed. I then used the Advanced Options to select which columns to send in the email and made sure to exclude my JOIN column to keep things looking clean.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
AMAZING WORKAROUND PAUL!!!! Exactly what I needed!
Thank you so much!
Peter
-
Happy to help!
Sometimes the hairbrained ideas actually work! Hahaha
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!