IF/AND or ISBLANK formula?

Jennifer Dewhurst

I am trying to evaluate three columns for the ABSCENCE of data to return a value of "Delinquent". For example: 

 

I would like the top row of this image to reflect the word "Delinquent" when none of the boxes are checked.  Should I use an ISBLANK formula or an IF/AND formula?  And what would it look like?   Any help on this is GREATLY appreciated!  Thank you so much. 

Jennifer Dewhurst

I think I could also do this by automating RYG balls? Only I just need Red or Green (no yellow). Would this be a better solution?

Andre Gien

Wow Craig you are a real Guru thank you for all you help.

 

Is there anyway that if it is "Delinquent" that an email update can be sent to somone notifying them that there is a potential problem 

 

Thanks so much again 

Andre 

 

 

J. Craig Williams

Jennifer,

 

I assume as long as one of the 3 (learning?) choices have been chosen, the status is not delinquint?

 

You've thrown a small wrench in the works with those hidden columns, but still, if the answer to the above is yes, this should work:

 

=IF(NOT(OR([Attended Meeeting]42, [Watched Video]42, [Read Pkg]42)), "Delinquent")

 

Note that if the columns were consecutive, I would use this instead:

 

=IF(COUNTIF([Attended Meeeting]43:[Read Pkg]43, 1) = 0, "Delinquent", "")

 

Cheers,

 

Craig

J. Craig Williams

Andre,

 

Thank you for the compliment and you are welcome.

 

To answer your question, sort of.

Check out Notifications.

https://help.smartsheet.com/articles/542904-using-notifications

 

Notifications, however, are only looking for a change, not a specific change.

To get around this, a Checkbox column can be added to 'watch' for Delinquent and then trigger the Notification when that is changed

 

=IF([Delinquent ]23= "Delinqent", 1, 0)

 

for row 23.

 

Drawback: Sends a notification email when it turns back from checked to unchecked (no longer delinquent)

 

Conditional notifications are coming, so we are told.

 

Craig

 

Jennifer Dewhurst

This is awesome Craig. Thank you so much!

J. Craig Williams

You also are welcome Jennifer.

 

Craig