Formulas & Filter Assistance for a To Do List

Elise Horgan

Hi, I have tried a few searches and haven't quite found what I need to perform the function within my smartsheet. Hoping some of the Formula wizards can help me out.

I am using Smartsheet to manage my to do list.

The filters are great but only make allowances for 1 or ALL conditions, I require 2.

I am wanting to create a formula, likely an IF AND statement to flag all priority rows 3-5 AND alert me when the Due Date is 7 days out from today.

This is what I have got but it is not working as I would like.

=IF(AND([Due Date]@row >= TODAY(7), Priority@row <= "3"), "Red", "Green")

The intent is to flag all priority time-sensitive tasks in a filter, remove completed from this view and for to update automatically with the data provided.

最好的方法是什么有一列汽车populat吗ed with a set list of text? eg. When Priority 1 is flagged against a row it populates text "low priority - not time critical"

What is the best way to have a date/ time auto stamped on a row upon the "done" checkbox being marked as complete

Appreciate any and all help.

Elise Horgan

T o Do List 2021-03-04_173626.jpg

Andrée Starå

Hi@Elise Horgan

I hope you're well and safe!

Here's a quick answer to your second part.

If you want to store the time.

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

If you only need to store the date you can use the Record a Date feature.

More info.

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action

Would that work/help?

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!

Bassam Khalil

Hi @Elise Horgan

Hope you are fine, to complete the excellent idea that @Andrée Staråposted, first for your formula when the Due Date is 7 days out from today. you mean a date in the past then the formula must be :

=IF(AND([Due Date]@row <= TODAY(-7), Priority@row <= "3"), "Red", "Green")

and for the other part of your question, you can have a column auto-populated with a setlist of text by using IF formula or by Vlookup with a table of a setlist of text that you want to be auto-populated.