conditional formatting help

[d3ell]

Hi All,

I have attached a workbook where im trying to utilize a traffic light stsem to see if a job is acceptable.

the 3 things are important are, the status column, the pvgis score and the number of panels.

i require the status bar to be blank if both the PVGIS score and No of panels is empty
i then require the status to be amber if the PVGIS score is 850 or above. if the score is below 850 i need it to go red.

then, next to be entered will be the no of panels. i need the status to turn green if the no of panels is 10 or above and i need the status to turn red if the no of panels is below 10.

it seems to be working apart from if the no of panels is below 10 then it doesnt turn red! i also need to ensure that if there is no figure in "no of panels" that it doesnt affect the status if the PVGIS score.

excel help1.xlsxPlease see attcahed it will make it a bit clearer.

[NBVC]

Try removing all current conditional formats in column B.. then apply these applied to range =$B$2:$B$1099:

For Green:

=$P2>=10

For Amber:

=$O2>=850

For Red:

=OR(AND($O2>0,$O2<850),AND($P2>0,$P2<10))

Is that the right combo?

[d3ell]

No, not quite working, the status is not changing to red if the no of panels is below 10. Its changing green if the number is above 10.

Do i need to have these in a specific order? i've tried several ways but no joy yet.

Any ones need "stop if true" un-checked?

Thanks for your help so far!

[d3ell]

No, not quite working, the status is not changing to red if the no of panels is below 10. Its changing green if the number is above 10.

Do i need to have these in a specific order? i've tried several ways but no joy yet.

Any ones need "stop if true" un-checked?

Thanks for your help so far!

[NBVC]

See attached for my setup....

Note: I added another condition to a couple of the CF formulas to check if the field is numeric since I see you may enter na sometimes...

[d3ell]

Yes, that works. I have however come across a little issue!

IF a score of below 850 is recorded the status goes red.. GOOD! but if 10 panels is put in the next box(COLUMN P) it then goes green! Grrr!

This is not a major issue if it cant be reolved as its usually a two step process....
step 1 : PVGIS SCORE
step 2 : No of PANELS

however on some occasions we will have both bits of info and they may get inputted at the same time or back to front.

is there possibly a way to get it to go red if just one of the criteria isnt met?

Thanks again!

[NBVC]

Try moving the Red condition up to the top of the priorities...

[d3ell]

Ha, thanks!

You've been most helpful!

I'll rep you up and mark as solved!

Thanks buddy!

[d3ell]

Ha, thanks!

You've been most helpful!

I'll rep you up and mark as solved!

Thanks buddy!

[d3ell]

Ha, thanks!

You've been most helpful!

I'll rep you up and mark as solved!

Thanks buddy!