Formula to show if certain cells are blank change the status to red, yellow, or green

I have a sheet where we want to show if the columns successor 1, 2, and 3 are blank the status column should be red.

If only successor 1 is filled out status should be yellow

If successor 1 and 2 are filled out staus should also be yellow

If all 3 successor columns are filled out the status should be green. Pretty much trying to show the status of completion for these columns.

I've tried if(not(isblank and a few other formulas and can't find the right way to write this. Does anyone have any ideas?

Screen Shot 2020-01-09 at 12.06.33 PM.png

Find more posts tagged with

Formulas

functionality

Accepted answers

Andrée Starå

Sorry about that!

I added it in the wrong order.

You should put the last one first.

Make sense?

All comments

Andrée Starå

Hi Taylor,

Try something like this.

=IF(OR([Successor 1]@row <> ""; [Successor 2]@row <> ""); "Yellow"; 
IF([Successor 3]@row <> ""; "Green"))

The same version but with the below changes for your and others convenience.

=IF(OR([Successor 1]@row <> "", [Successor 2]@row <> ""), "Yellow", 
IF([Successor 3]@row <> "", "Green"))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

taylor.thompson89836

That has definitely worked better than anything I've tried so far! i'm now just running into the problem where if I have anything in the successor 1 column it will keep it Yellow, even when I fill something into the Successor 3 column.

Andrée Starå

Sorry about that!

I added it in the wrong order.

You should put the last one first.

Make sense?

taylor.thompson89836

That worked!! Thank you so much!

Andrée Starå

Excellent!

Happy to help!

Here's the formula for others reference.

=IF([Successor 3]@row <> ""; "Green"; IF(OR([Successor 1]@row <> ""; [Successor 2]@row <> ""); "Yellow"))

The same version but with the below changes for your and others convenience.

=IF([Successor 3]@row <> "", "Green", IF(OR([Successor 1]@row <> "", [Successor 2]@row <> ""), "Yellow"))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

✅Remember! Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.