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:

Hi all,<\/p>

I hope you're well and safe!<\/p>

I've developed a solution that lets you store the date or value.<\/strong><\/p>

You'd use the copy-row automation and a VLOOKUP or combination of INDEX\/MATCH to make it work.<\/p>

We'd trigger the copy-row to another sheet and get the created date\/other value and then use the VLOOKUP\/INDEX\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\/value that we can use, it will work.<\/p>

Also, this opens up more options with auto-numbering and similar.<\/p>

Depending on other workflows, you could reuse one sheet for multiple workflows.<\/p>

I hope that helps!<\/p>

Let me know if you have any questions!<\/p>

Stay safe and have a fantastic day!<\/p>

Best,<\/p>

Andrée Starå<\/p>

Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>","bodyRaw":"[{\"insert\":\"Hi all,\\n\\nI hope you're well and safe!\\n\\nI've developed a solution that lets you \"},{\"attributes\":{\"bold\":true},\"insert\":\"store the date or value.\"},{\"insert\":\"\\n\\nYou'd use the copy-row automation and a VLOOKUP or combination of INDEX\\\/MATCH to make it work.\\nWe'd trigger the copy-row to another sheet and get the created date\\\/other value and then use the VLOOKUP\\\/INDEX\\\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\\\/value that we can use, it will work.\\n\\nAlso, this opens up more options with auto-numbering and similar.\\nDepending on other workflows, you could reuse one sheet for multiple workflows.\\n\\nI hope that helps!\\n\\nLet me know if you have any questions!\\n\\nStay safe and have a fantastic day!\\n\\nBest,\\n\\nAndrée Starå\\n\\nWorkflow Consultant \\\/ CEO @ \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/workbold.com\\\/\"},\"insert\":\"WORK BOLD\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2020-05-24T11:36:34+00:00","insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/68589\/lock-or-store-date-value-solution-without-using-zapier","embedType":"quote","name":"Lock or Store Date\/Value Solution without using Zapier"}"> 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 bymarking 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.