Formula Problem with Multiple IF / AND Statements and Multiple outcome?

Hi there,

This is my first post but I often seek help for excel queries so Im sure it won't be my last.

I am trying to perform the following:

----
If $D$5 = "SET" AND $E$5 = between 39.0 and 41.0 then display value entered and make $E$5 have Green background

BUT

if $D$5 = "CHECK" AND $E$5 = between 37.0 and 43.0 then display value and make $E$5 Green background


If the value entered is outside of these tolerances OR "SET / CHECK" is not entered in $D$5 then make $E$5 have RED background
----

For background info' my staff have to perform quality checks on the scales of a measuring device.

If they enter "SET" then the tolerance of that scale is between 39.0 and 41.0. If it passes then the cell should turn Green. If it fails it should turn Red.

If they enter "CHECK" then the tolerance of that scale is between 37.0 and 43.0. If it passes then that cell should turn green. If it fails it should turn Red.

If they fail to write either "SET" or "Check" then it should either tell them, or for simplicity just turn red.


I have tried contional formatting but couldn't get this to work with the "SET/CHECK" input.

I also tried the following (this doesn't include changing cell colour) and it also didn't work:
=IF($D$5="SET",IF($E$5<40.1,IF($E$5>38.9,IF($D$5="CHECK",IF(E5<43.1,IF(E5>36.9,E5))))))


I have reached the limits of my Excel knowledge.
Thanks in advance for your assistance.

John.

Just seen your book.

Thankyou!

Is it possible to have it apply to the cell where the value is inputted rather than a seperate cell? (Doesn't have to say fail, just keep the value entered)

Thanks again!

GOT IT!

Had to have 4 rules:

Kept the 2 rules above (CF)
Then also had to add:

Please Login or Register  to view this content.

+

Please Login or Register  to view this content.

and select RED for formatting.

Thanks so much! - I spent about 2 hours on this earlier.

So glad I joined.

Thanks again - John.