How to stop Alerts being triggered by TODAY() formula

Peter D

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

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, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Peter D

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 Newcome

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

Andrée Starå

Did you get it working?

Best,

Andrée

Peter D

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

Andrée Starå

Paul,

Great workaround! (if it works) 

Best,

Andrée

Andrée Starå

Let us know if it works with Pauls workaround!

Best,

Andrée

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

Paul Newcome

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.

Peter D

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

Thank you so much! 

Peter

 

Paul Newcome

Happy to help!

 

Sometimes the hairbrained ideas actually work! Hahaha