New to Smartsheet - Setting alerts once status has changed

Daniel Gill 97

Hi all,

I hope you are all doing well.

I have tried to incorporate Smartsheet into my daily workings and hope you may be able to help. Seemingly, Excel is far easier when setting rules, but with automation needed I decided to use Smartsheet.

Currently, I have a project on hold sheet which records all projects that are placed on hold. I need to be aware when a project has been on hold for 90 days, so created =[Column6]@row + 90.

Then, I created a status column (Believe this is what I need) and created =IF([Column8]@row < TODAY(+5), 1, 0). This helps me identify when a project is above 5 days or below.

I was going to use this to then create an alert to when the status changes from 0 to 1, an automated reminder is sent.

This is obviously extremely basic, for one, I would rather have 3 choices. Yes with the green tick (Plus 5 days from todays date), Hold with the orange exclamation mark (between 5 and 1 days from todays date) and red with the X mark (Todays date and any date past).

Too, I cannot work out how to get the status column to actually show statuses rather than 1 and 0 in my original IF statement.

All of the above I have picked from various comments on the community, so if there are easier ways please feel free to advise. I'm all ears (Almost literally).

Thanks,

Dan

Kimberly Loveless

I am glad that it is working for that one. The yes one, as it is currently written, unless the date is exactly 5 days away it wouldn't show. If you want it to show the yes/green for anything 5 days or more away it is just adding a greater than symbol before the= in the formula.

As for the hold, I think that it should have a an = in part of it as well.

Try this one, all I did was add the two symbols I noted above

=IF([Column8]@row >= TODAY(+5), "Yes", IF(AND([Column8]@row < TODAY(+5), [Column8]@row >= TODAY(+1)), "Hold", IF([Column8]@row <= TODAY(), "No")))

Kimberly Loveless

Ok so first the status is showing 0 or 1 because that is the value used in your formula I would guess the community post you pulled it from was referring to a checkbox column type (where 1 would be checked and 0 unchecked).

You can use the symbols you described for your status column, you would just need to edit your formula a little to become a nested if statement. So for this formula I am using the column set up below....

=IF([Column8]@row = TODAY(+5),"Yes", IF(AND([Column8]@row < TODAY(+5),[Column8]@row>TODAY(+1)),"Hold",IF([Column8]@row <=TODAY(),"No")))

In that formula the values in the quotation marks are the options of the symbols (as shown below) '

Then you can decide what you would like to trigger the automation but it would look like this if you wanted it to signify any change to status. (I also set it as an alert instead of a reminder but you can set it as either.)

You could also set multiple versions of the alert/reminder to happen and sent specific messages based on the status as well.

Daniel Gill 97

https://community.smartsheet.com/discussion/comment/306875#Comment_306875

Thank you so much Kimberly,

You are a star!!

This works perfectly in my Smartsheet and already is working wonders for any down as no.

For Yes and Hold, they are blank and only until they change to No does it show a Symbol.

Any ideas? :)

Dan

Daniel Gill 97

Kimberly Loveless

I am glad that it is working for that one. The yes one, as it is currently written, unless the date is exactly 5 days away it wouldn't show. If you want it to show the yes/green for anything 5 days or more away it is just adding a greater than symbol before the= in the formula.

As for the hold, I think that it should have a an = in part of it as well.

Try this one, all I did was add the two symbols I noted above

=IF([Column8]@row >= TODAY(+5), "Yes", IF(AND([Column8]@row < TODAY(+5), [Column8]@row >= TODAY(+1)), "Hold", IF([Column8]@row <= TODAY(), "No")))

Daniel Gill 97

https://community.smartsheet.com/discussion/comment/306949#Comment_306949

Thanks again Kimberly,

I'm an apprentice at my company and totally new to formulas never mind Smartsheet!

This has worked and I am please to share my team are extremely happy with it!

I hope you have a lovely day today, you truly have helped a lot.

Dan :)

Kimberly Loveless

Glad I was able to help @Gerhard da Costa Pinto@Daniel Gill 97 😀