Conditionally format rows of information

[magimagpie]

Hiya, I'm hoping somebody can help.

I want to use conditional formatting in the end column called all courses complete. I want the end column to be red and say no until all 6 courses have been completed.

Once I put all 6 dates into each course column I want the end column to turn green and say yes.

So both the Sarah's end column would be green and say yes. Both the John's end column would be red and say no.

I'm struggling to track at the moment as not all courses can be completed in one go but they make up one full training session.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]COURSE 1[/TD]
[TD]COURSE 2[/TD]
[TD]COURSE 3[/TD]
[TD]COURSE 4[/TD]
[TD]COURSE 5[/TD]
[TD]COURSE 6[/TD]
[TD]ALL COURSES COMPLETE?[/TD]
[/TR]
[TR]
[TD]SARAH SMITH[/TD]
[TD]01/10/2018[/TD]
[TD]01/10/2018[/TD]
[TD]01/10/2018[/TD]
[TD]01/10/2018[/TD]
[TD]01/10/2018[/TD]
[TD]01/10/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN SMITH[/TD]
[TD]01/10/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SARAH JONES[/TD]
[TD]01/10/2018[/TD]
[TD]01/10/2018[/TD]
[TD]01/10/2018[/TD]
[TD]01/10/2018[/TD]
[TD]01/10/2018[/TD]
[TD]01/10/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN JONES[/TD]
[TD][/TD]
[TD]01/10/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jmacleary]

Re: Help to conditionally format rows of information

Hi there ans welcome to the forum. Assuming your data starts in A1, put this formula in H2 : =IF(COUNT($B2:$G2)=6,"Yes","No") and copy down to all used rows. Then create 2 conditional formatting rules for the H column. 1 rule for cell value equal to Yes with a green fill, and another rule with cell value of No with a fill colour red.

 

[magimagpie]

Re: Help to conditionally format rows of information

Fab, that worked thank you!

I have copied the formula down into rows that don't have data yet so that the formatting is there in anticipation of new staff but it is obviously now displaying no as there is no data in these 6 cells.

Is there a way that you know of where the word yes and no will disappear whilst there is no data in that row...hope that makes sense.

 

[jmacleary]

Re: Help to conditionally format rows of information

Hello again. Replace that formula with this one: =IF(ISBLANK(A2),"",IF(COUNT($B2:$G2)=6,"Yes","No"))

What it does is look for a name (well actually any non- blank value) in column A, and if its blank sets the cell value to blank, otherwise it does the test as before.

Glad I could help

 

[magimagpie]

Re: Help to conditionally format rows of information

It's saved me hours of googling, I can't thank you enough!

 

[jmacleary]

Re: Help to conditionally format rows of information

Youre welcome. Can you click the thanks and the likes buttons on my last post for me please?