Welcome!

Logic Needed for Turning on the Gray Stoplight

I am trying to add logic in the following string that will turn on the "Gray" stop light ball if the % Complete cell is left blank.

=IF([% Complete]18 < 0.5, "Red", IF(AND([% Complete]18 >= 0.5, [% Complete]18 < 1), "Yellow", IF([% Complete]19= 1, "Green", IF([% Complete]19= blank, "Gray")))

I have tried "blank" and "null" and neither one works. Any of you super users out there figured this out yet? Thanks.

Find more posts tagged with

Comments

Todd Harrison

All of those should say [% Complete]18. I know that is not the issue. Thanks.

Mike Wilday

Try using isblank in the IF statement.

https://help.smartsheet.com/function/isblank

=IF([% Complete]18 < 0.5, "Red", IF(AND([% Complete]18 >= 0.5, [% Complete]18 < 1), "Yellow", IF([% Complete]19= 1, "Green", IF(ISBLANK([% Complete]18), "Gray")))

Todd Harrison

Thanks, Mike. That worked but only after I moved the Gray logic to the front of the logic string. You rock!

Mike Wilday

That is probably because one of your other flags are true and firing first.

Mike Wilday

Glad I could help you out.

Sign in to join the conversation!

Quick Links

Trending discussions

Is it possible to edit an email list in an automated monthly report?
I have a monthly report I send out that has a long list of recipients. I know I can edit the recurrence of it being sent, but does anyone know of an easy way to just add or take out people that get my email without having to recreate the whole thing?
User Friendly Search
We would like to allow conference attendees to check themselves in using tablets at the conference center. I know there is a "find in sheet" magnifying glass in table view, but it is not super obvious, especially for non-Smartsheet users. Is there a better way for them to easily find their row among all the attendees so…
Update Requests included in separate Notification Automation
Hi! We have a sheet where team members are triggered to complete an Update Request when a line is assigned to them, which of course includes its own messaging. We also have a separate automation which sends out a weekly 'reminder' email for all assigned lines which have not been updated to a 'completed' status. My question…