I am trying to make a google spreadsheet that I can actively type names into, and when I then type those same names into the adjacent column, they grey out the same name in the previous column, while keeping the ones that don't repeat un-highlighted. I want to be able to type them in any order and still have it work. Right now, it sort of works, however, it only greys out the previous names if the new names in the right column are in a lower row than the ones in the left. This may not make sense, so look at the attached image. The code on the top of the screenshot is what I have entered into the conditional format rules, see 2nd screenshot. I'm quite new to google sheet equations and I've been trying to figure this out for awhile. Anybody know how to accomplish this? Thank You.

CodePudding user response：

To solve your issue, I will first create a helper sheet (you can do it within same sheet also) for a result used as a reference:

=arrayformula(IF(Sheet16!B2:B=Sheet16!C2:C,True,False))

Return True if the column value is equal to adjacent column

On your main sheet, I will enter the custom formula for conditional formatting and apply to the range you need:

=indirect("Helper!C2:C")=TRUE

Here is the final result, does it help?

CodePudding user response：

Try this for the column you need to grey out:

=AND(A1 <> ""; MATCH(A1; B:B;))

If you apply it to B19:B33 and want to look only in C13:C33 it will look like this:

=AND(B13 <> ""; MATCH(B13; C$13:C$33;))