Excel conditional formatting help.

[Colbaker]

Hi all,

I am struggling with conditional formatting at the moment and was wondering if any excel guru's could help please.

What I am trying to do is make Cell A1 turn green if Cells A2 to A10 have only "Full" and "Not Required" written in them, I also want Cell A1 to be orange if Cells A2 to A10 have the words "Needs*" and "not required" but none of the cells have "Full" in them. I also need Cell A1 to be red if Cells A2 to A10 only have "Not Required" written in them.

can you help?

I assume I need to use the "use a formula to determine which cells to format" bit, oh and its Excel 2010

 

[bazzabear]

I'm not sure conditional formatting is that powerful.

Nut you could use nested IF statements to enter a value in a hidden cell based on your rules, and then get your conditional formatting to simply look at that one cell instead of lots.

 

[413x]

i think this can be done via formula except you have conflicting IF

green if only Full and (is this OR?) Not Required
red if all Not Required

 

[Colbaker]

I'm not sure conditional formatting is that powerful.

Nut you could use nested IF statements to enter a value in a hidden cell based on your rules, and then get your conditional formatting to simply look at that one cell instead of lots.

What sort of if statement would you suggest to return the values required?

 

[413x]

=IF(COUNTIF(A2:A8,"NR")=7,"R",IF(COUNTIF(A2:A8,"F")+COUNTIF(A2:A8,"NR")=7,"G","O"))

is close
but im not quite sure what you want orange to be

that would be the formula in cell A1
then conditional would be
A1 = R then red
A1 = O then orange
A1 = G then green

ive used F as full and NR as not required for tidyness

 

[Colbaker]

i think this can be done via formula except you have conflicting IF

green if only Full and (is this OR?) Not Required
red if all Not Required

Nope it's and required for green, basically it's a traffic light colour scheme and in cell A1 will be a ship name and in cells A2 to A10 will indicate 9 members of staff, some of these members of staff need key access to this branch and some never will (hence why there will be "Not required", I want the branch cell (A1) to turn green if all of the staff that need access have full, if that staffs needs something I.e Needs keys, Needs alarm then A1 needs to turn orange which is why I put the wildcard at "needs*" and if it is a bran where no one needs access I want it to turn red as all staff will have "not required" under there column.

Hopefully I have explained better what I am trying

 

[413x]

im trying to figure if you can use Else orange basically!
i can see the possibility of needs* and Full beung in the same list without a colour specified (according to what you want)

is this right..if all cells a2 to a10 have at least one full and not required in rest then green
if all Not required then red

 

[Colbaker]

im trying to figure if you can use Else orange basically!
i can see the possibility of needs* and Full beung in the same list without a colour specified (according to what you want)

is this right..if all cells a2 to a10 have at least one full and not required in rest then green
if all Not required then red

yeah that's correct, and Orange will be with some of the cells having Needs*.

 

[413x]

you could see if this works but put it in an unused cell (ie D1 for my example)

=IF(COUNTIF(A2:A10,"Not Required")=9,"R",IF(COUNTIF(A2:A10,"Full" )+COUNTIF(A2:A10,"Not Rrequired")=9,"G",IF(COUNTIF(A2:A10,"Needs *")>0,"O","X")))

make the conditional formatting reference this cell and turn cell cell A1
- green if D1 = G,
- orange if D1 = O
- red if D1 = R

that would be how would do it

im not sure if i have all the criteria right
if you want to understand the formula i will explain

Edit,
i havent exactly used conditional formatting like you asked but i have no done what you wanted i think.
details to follow

 

[413x]

 

[Colbaker]

Woo Hoo, That worked brilliantly thanks.

I understand the COUNTIF formula mostly but not sure why there is =9 at the end?

Also just realized for some reason the RED one doesn't work and i can't work out why, I think it has something to do with when A2>A10 is NR cell A1 is still green for some reason

 

[413x]

Woo Hoo, That worked brilliantly thanks.

I understand the COUNTIF formula mostly but not sure why there is =9 at the end?

Also just realized for some reason the RED one doesn't work and i can't work out why, I think it has something to do with when A2>A10 is NR cell A1 is still green for some reason

Equals 9 is at end as there are 9 cells. So for each one that meets the criteria 1 Is added. I gathered you wanted red when all are NR. That would make the total 9. Ie. Only red when all are NR. If equals 9 wasn't at the end you are not telling it to meet a criteria as you are just counting the cells with NR. =9 is the activator of the formating
Hope this makes sense I'm writing from the gym

For the green I thought you wanted NR OR Full in all the cells. So same as above. Sum of COUNTIF must equal 9 to be Green

Is the red one at the top of the list?
If you look green and red can both be active if NR is in all 9 cells
I didn't bother to change this as I thought the red being at the top meant in event of conflict the top entry took priority
If this isn't the case I will make an adjustment!

 

[Colbaker]

Equals 9 is at end as there are 9 cells. So for each one that meets the criteria 1 Is added. I gathered you wanted red when all are NR. That would make the total 9. Ie. Only red when all are NR. If equals 9 wasn't at the end you are not telling it to meet a criteria as you are just counting the cells with NR. =9 is the activator of the formating
Hope this makes sense I'm writing from the gym

For the green I thought you wanted NR OR Full in all the cells. So same as above. Sum of COUNTIF must equal 9 to be Green

Is the red one at the top of the list?
If you look green and red can both be active if NR is in all 9 cells
I didn't bother to change this as I thought the red being at the top meant in event of conflict the top entry took priority
If this isn't the case I will make an adjustment!

Green only if all cells have Full and NR.
Orange only if cells have Full, NR and Needs*
Red if only NR and possibly No Access.

 

[413x]

On my machine if all are NR mine switches to red

if you have another option for red criteria you will need to amend the red equation to

=(countif(A2:A10,"NR")+countif(A2:A10,"NA"))=9

 

[Colbaker]

On my machine if all are NR mine switches to red

if you have another option for red criteria you will need to amend the red equation to

=(countif(A2:A10,"NR")+countif(A2:A10,"NA"))=9

Thanks I'll give that a try tomorrow and let you know, might not be working as I am using drop down lists and I have had situation were excel has not recognised when I change something in the drop down.

 

[413x]

possibly. im fairly new to doing this sort of stuff so things like that may or may not affect it.
but i thought as long as the red is above the green in the 'conditional formatting rules manager' it should work