New to Smartsheet - Setting alerts once status has changed

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

Best Answer

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭
    Answer ✓

    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")))

Answers

  • Kimberly Loveless
    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
    edited 10/05/21

    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

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭
    Answer ✓

    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")))

  • 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 :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!