If Statement for Colored Balls

RingJake

Hello,

I am trying to create an IF statement that says IF there is one or more red ball in the range below then the green ball turns red OR If there are one or more yellow balls I would like the green ball to turn yellow. Otherwise, the ball stays Green. So far here is a simple attempt just trying with the first portion of the function, but I am unable to get this to work.

=IF(Health2:Health19 >=1 Red, Red)

Can somebody please help me?

Etienne Mermillod

The contains formula seems to work fine:

if any value is red, the cell be be red

else if any value is yellow the cell will be red

else the cell will be green :

=IF(CONTAINS("Red", Health:Health), "Red", IF(CONTAINS("Yellow", Health:Health), "Yellow", "Green"))

RingJake

Excellent @Etienne Mermillod Thank you!

Now as a follow-up would it be possible to have instead of a range being:

Health:Health or Health1:Health20

It would be possible to always have this applied to the next 19 rows under the top level task? That way the function can stay together when it is sorted as well as easy to copy and paste for new groupings following the format in the screenshot.

Etienne Mermillod

Yeah, specifying a limited range is also possible (just make sure then to not include the current row in the range, such as :

=IF(CONTAINS("Red", Health2:Health20), "Red", IF(CONTAINS("Yellow", Health2:Health20), "Yellow", "Green"))

RingJake

Hey @Etienne Mermillod,

Yes, but the range does not seem stay together when I sort. This is impacted other columns

I have as well.

RingJake

If there was a way to group or lock certain rows that would ideally be the way to accomplish this?

Etienne Mermillod

There isn't something you can do this kind of lock.

RingJake

Hi @Etienne Mermillod,

What it looks like I really need is the ability to sort only by the Parent rows, while keeping the child rows in the same order. No ability to do this?

Etienne Mermillod

Adding another row, that will be sued for sorting could be a solution, but I need to know more about the requirements.

RingJake

Hey @Etienne Mermillod,

It seems if I drag the parent row to where it needs to be it will take the child cells with it in the desired order.

But the automation of this would be to sort it. As you can see in the screenshot above I have many teams providing updates to a program, these updates make up a status and % complete. I would like to be able to sort only by the parent's status or % complete without changing the order of the children. Additionally, to have the Parents in alphabetical order without changing the children.

Andrée Starå

Hi @RingJake

I hope you're well and safe!

To add to Etienne's excellent answer/advice.

Have you explored using a report instead?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Indira Buike

Hi! piggybacking on this question, I need it that if I have 2 red balls in the status column then the health column would be red, if I have 1 red ball then is yellow and if I have none red balls then is green. I'm stuck in the formula, pls help :(

Andrée Starå

Hi @Indira Buike

I've answered your other similar question.

=IF(COUNTIF(Status3:Status6, "Green") = 4, "Green", IF(COUNTIF(Status3:Status6, "Green") = 3, "Yellow", IF(COUNTIF(Status3:Status6, "Green") = 2, "Red", "")))

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!