Workflow or function that alerts me when something repeats
I would like to set up an automation or function that alerts me when something is repeated in a sheet 3 times in a 30-day period. The trigger could be taken from a drop down menu, i.e the location "ABC Lane" has been chosen in a row from the drop down on 06/01, 06/05, and 06/21" . I do not see something like this in the automation field and I am unsure if there is a formula/function that could accomplish this.
Answers
-
Do you record the date when the dropdown is selected?
-
Yes. It is recorded in a column (separate from the drop down list) for every row.
-
Ok. In a flag or checkbox type column, you would enter something along the lines of this:
=IF(COUNTIFS(Location:Location, Location@row, [Date column]:[Date Column], @cell>= TODAY(-30)) >= 3, 1)
Basically we count the number of rows that have a matching location and a date within the past 30 days and say that if that count is 3 or more then check/flag.
-
Thank you! And if I wanted to add a second criteria to the formula, can I use an AND function here? For example, if the location appears 3 times in a 30 day period AND the classification of the location is either "reportable" or "investigated", where would I pull this into the formula?
-
It depends on where it needs to be incorporated. Are you wanting it to be a criteria for the COUNTIFS, or are you meaning if the classification on the existing row is one of those two options?
-
The latter. Basically I want something that does: If the "location" is repeated 3 times in a 30 day period AND this repeated location is also classified as "reportable" or "investigated", then the flag column will be flagged.
-
Ok. Try this...
=IF(OR(Classification@row = "Reportable", Classification@row = "Investigated"), IF(COUNTIFS(Location:Location, Location@row, [Date column]:[Date Column], @cell>= TODAY(-30)) >= 3, 1))
-
Thanks!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!