How to stop Alerts being triggered by TODAY() formula

03/26/19 Edited 12/09/19

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

SmartSheet - Screengrab.PNG

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    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, [email protected])

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You can specify fields by using the "+ Specify a change" option. See screenshot below.

    Community.PNG

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Did you get it working?

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Paul,

    Great workaround! (if it works) wink

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Let us know if it works with Pauls workaround!

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    It actually worked!! Haha. cool

     

    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.

    thinkspi.com

  • AMAZING WORKAROUND PAUL!!!! Exactly what I needed! 

    Thank you so much! laugh

    Peter

     

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

     

    Sometimes the hairbrained ideas actually work! Hahaha 

    thinkspi.com

Sign In or Register to comment.