Conditional format Error?

[Bebbspoke]

Hi peoples,

NEW col C is populated by the formula =if($A1=1,B1,"") filled down

B is occasionaly a whole number (say) 3 or above - I wish to highlight such occurence.

Whe I apply the Conditional format (say) >2.9 the entire Col C highlights???

Please - there is surely a simple fix for this but darned if I can find it!!!

Your suggestions, thank you.

 

[Joyner]

If you are in cell C1, highlight the entire column C range and the conditional format formula in C1 should be =C1>=3, if I understand correctly that should work

 

[Bebbspoke]

Hi Joyner, thank you for your response... the situation is a little more complex but I thought I'd describe it simply & take it from there... (definitely something wrong with my logic)!

So for real:

Col AF may or may not have entry "a" whereas Col AG may or may not have entry "b", and Col AJ has the numeric.

Conditional format

=AND(AF1+"a",AJ1>=3 where font = black & fill = green is OK

but

=AND(AG1="b",AJ1>=3 where font = white & fill = green fails...

it correctly whites the font with green fills in AJn cells where numeric is >=3

but ALSO green fills AJn cells with NO numeric IF respective AGn cell = "b"

this is somewhat puzzling!!!

Thank you.

 

[Joyner]

Ok, I am not following.

I get this:

Col AF may or may not have entry "a" whereas Col AG may or may not have entry "b", and Col AJ has the numeric.

What do you want to do if AF = "a" and AJ >=3
What do you want to do if AG = "b" and AJ >=3
Can both AF = "a" and AG = "b" and AJ >=3, if so what do you want to do.

Do you want one formula like:

=AND(OR(AF1="a",AG1="b"),AJ1>=3)

One thing to make sure of, if your tried numerous formulas in the conditional formatting, make sure you delete them so there are no conflicting formulas messing everything up.

 

[Bebbspoke]

Hi Joyner - req is as follows: -

Col AJ numeric is derived elswhere, the fill is ONLY green IF value >= 3

The paths to define AF="a" and AG="b" are different but AF1="a" and AG1="b" CANNOT occur, such is NOT POSSIBLE on the data clocking.

IF AF ="a" and AJ >=3 then font = black and fill = green

IF AG ="b" and AJ >=3 then font = white and fill = green

AJ <3 CANNOT occur by the formulae used to determine the numeric

Thank you for your interest in this problem, I trust the req has been clarified... & yes, I ensure to delete all Conditional formulae in AJ in my attempts to resolve!

Cheers, Bebbspoke

 

[Joyner]

So if I understand correctly, where ever you want the conditional formatting, highlight the range, and the formula would be

=AND($AF1="a",$AJ1>=3)


=AND($AG1="b",$AJ1>=3)

and set your formatting as desired.

 

[Bebbspoke]

Hi Joyden - I had tried that previously - and have just discovered that simply clearing the rules from the relevant Cols does not appear to actually clear all the rules.... even though such is implied!.... so I stripped all rules out and started to reapply them...
The problem recurs - I'm baffled by this but there are far more important things to do with the processing on the spreadsheet...
I'll throw in the towel for present, thank you for your assistance, Bebbspoke