How do I create a formula to activate a flag if a column has not been changed?

Tracie Beckstrom

I want to be able to send an automatic update request when people fail to add updates to a text column (Called Updates). Currently, this doesn't seem possible. But I thought of a workaround that may work if someone could point me in the direction of writing a formula. I thought of having a flag column that will go up if someone in a contact list column does not change a particular column in the last 10 days. And then send an automatic update request to go out whenever that flag is up. Any ideas of how I would write this formula for the flag column?

David Joyeuse

Hi Tracie!

You'll need to have the automated column "Date Modified" added to your sheet.

Then use a flag type column with this formula:

=IF(TODAY()>[Date Modified]@row+10,1,0)

The flag will raise if no modification is done on the sheet within 10 days. No matter the cell.

If you only want the flag to raise if only the update cell is not modified, we'll have to investigate a little bit further :)

Tracie Beckstrom

Thank you! That gives me a starting place. And yes, I'll need to know if cells only in a particular column have not been modified.

Andrée Starå

Hi @Tracie Beckstrom

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

David Joyeuse

Hi @Tracie Beckstrom

What Andrée is proposing can be helpful in your case. Make sure copy the line to the sheet when they are created and have a rule when the updates cell is modified to copy the line on that same sheet.

Then you're gonna need 2 helper columns on your sheet.

• 1 to raise the flag of no updates in the last 10 days called [No Updates 10 days]
• 1 to check if the line is the last update of it, called [Last Update]

In the [last update] column, makes it a flag or star type symbol cell have this formula:

=IF(COUNTIFS([Main Column]:[Main Column],[Main Column]@row)-COUNTIFS([Main Column]:[Main Column]@row,[Main Column]@row)=0,1,0)

This will return a star if that line is the last with the value in [Main Column], adapt the formula with any column you have that can sort this out like project number or whatever so it doesn't mess with other project or else.

Then in the [No Updates 10 days] column use this formula:

=IF([Last Update]@row=1,IF(Today()>[Date created]@row+10,1,0),0)

It will raises a flag only if this cell is the last one of this project and if it hasn't been updated since the last 10 days.

Then you can go on with your automated workflow to blast managers that aren't updating cells :D

Hope it helped!

Paul Newcome

Here is another suggestion...

You could use the method outlined above to capture when the Updates column was last updated.

Then set your automation to run daily with a condition of when that date is not within the past 10 days.

This will help with organization and sheet performance since it cuts out both formulas and columns, and it increases reliability since it cuts out the TODAY function.

The TODAY function will only update when the sheet has been activated, so if no one opens the sheet or otherwise activates it, then the TODAY function will not update to the current date which in turn means your rows may not get flagged until more than 10 days have passed.