1

I had thought that my question here:

In a subset of cells, determine if at least one corresponding cell matches a value

would give me enough information that I could apply it to conditional formatting, but that is not the case. So, my follow-up question is this: given the following data, I would like to conditionally format the cells in column A if, say, their value is "SR" AND there is not at least one corresponding cell in column B that contains "*". By "corresponding cell" I mean the cell immediately to the right of an "SR".

     A     B
1    DR    *
2    DR    &
3    SR    &
4    DR    *
5    SR    #
6    SR    #

In the above example data, cells A3, A5 and A6 should be conditionally formatted because there is not an "*" to the right of any of those three cells. Cells A1, A2 and A4 should not be conditionally formatted because they do not contain "SR".

Community
  • 1
  • 1
Brian
  • 197
  • 2
  • 10
  • You can use COUNTIFS() to figure out whether there's at least one SR with a * in the same row. – Tim Williams Oct 22 '16 at 05:42
  • @SOreadytohelp: Yes, I can get a boolean response with "COUNTIFS(A:A, "SR",B:B, "~*" )=0", but I can't seem to make this work with conditional formatting. If the group of three "SR" cells, in the above example, doesn't have at least one associated "*", I want to conditionally format all of the "SR" cells (and only the "SR" cells; not the "DR" cells). – Brian Oct 22 '16 at 15:26

1 Answers1

1

Use this formula in conditional formatting

=AND(A1="SR",COUNTIFS(A:A,A1,B:B,"~*")=0)
Aditya Pansare
  • 1,112
  • 9
  • 14
  • This is producing a result, but not the correct one. For every cell that is the same in column A (say, all the cells that contain "SR"), if there is not at least one "*" to the right of one of those "DR" cells, I want all the "SR" cells to be conditionally formatted. What is happening with your solution is that ALL the cells in column A are being conditionally formatted (that is, all the "DR" cells as well as the "SR" cells. The "DR" cells, in this case, should not be conditionally formatted). – Brian Oct 22 '16 at 15:21
  • Also, is there a reason it would ever be less than 0? – Brian Oct 22 '16 at 16:55
  • please check the edited answer. this formula will give the result you are seeking for. And it would never be less than 0. – Aditya Pansare Oct 22 '16 at 17:53