Hello,
I currently have two formulas/rules conditionally formatting a range of cells.
They are (in order of application):
=AND(A2="",G2="") - This is set to format the cell in white (i.e. as if it had not been formatted)
=ISERROR(MATCH(G2,$H$31:$H$54,0)) - This formats the cell in red so that it is highlighted.
The purpose of this is to show me cases where G2 does not match a predefined list of values (H31:H54), but not highlight cases where G2 is blank, as long as A2 is blank also.
This works fine (although if G2 is filled correctly, and A2 is blank, it doesn't highlight, which confuses me, but doesn't matter to the functionality of my spreadsheet).
What I would like is to combine the two formulas together into one single rule.
I have tried
=OR(ISERROR(MATCH(G2,$H$31:$H$54,0)),NOT(AND(A2="",G2="")))
and several variations of this, but cannot get it to work.
Does anyone have any ideas?
Thanks very much,
Jess
P.S. If someone could also explain to me why =AND(A2="",G2="") works if A2 is filled and G2 is blank, but not the other way round, I'd be very interested!
Bookmarks