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.
Bookmarks