Conditional Formatting

[Millertime9460]

I created a mock wire list that is similar to what I have at work. The technicians usually highlight a physical copy when the wires are installed. Here's an example. 

So the table shows the wire's start and where it's terminated. This is for every connector ID. If someone installs one end of the wire and doesn't finish, they would usually highlight one side (illustrated in "Connector ID: TB1") and also highlight the same end in its destination's table (shown in "Connector ID: J1"). 

I want to create a relationship that, when column K = a particular side, it'll highlight the other side based on column J. For instance, if I am working on TB1 3C and only pin one side (halfway done), I would highlight the FROM side on row 15 and then the TO side on row 8. That is the same wire shown in column J (WIRE3). 

Here's what I was thinking in some pseudocode:

IF  $K6 == "RS"
{
      Make right side green
      Search Column J until duplicate WireID is found
      Make left side green
}

 

[#2 · Accepted solution]

@Millertime9460  Here is one option (it assumes you have excel 365). Custom formula in conditional formatting:

=($K6="LS")+($K6<>"RS")*(TRIM(CONCAT(FILTER($K$6:$K$17,$J$6:$J$17=$J6)))="RS")

A second one for the opposite side.

note this will highlight BOTH if this row has LS or the matching other row has RS

See attached. 

[#3]

Wow, that worked! Thanks a bunch!

[#4]

@mtarler How similar would it be if I wanted to do the same thing for "DONE"? If I make one row "DONE," it also matches its duplicate WireID as "DONE."

[#5]

@Millertime9460  should be nearly identical:

=($K6="DONE")+($K6<>"DONE")*(TRIM(CONCAT(FILTER($K$6:$K$17,$J$6:$J$17=$J6)))="DONE")

the TRIM(...) is just in case they have a space instead of an empty cell but you could also do something like: 

=ISNUMBER(SEARCH("DONE",CONCAT(FILTER($K$6:$K$17,$J$6:$J$17=$J6))))

so that if they say DONE ALL or DONE BOTH it still works in either or both locations. 

A similar trick could be used on the RS and LS items but would need to be done for each part since they are looking for different things.

[#6]

@mtarlerIf I want the corresponding row highlighted to return "DONE" in the status bar, how would I go about that? On my work computer, I've got a column that calculates the % of each row based on the text in the status column. So "DONE" would return 100%, "LS" or "RS" would return 50%. When the other end of the wire's corresponding row is highlighted, the % completion column doesn't recognize that it's really "DONE," so it won't return 100%.

[#7]

conditional formatting can't change values so you are talking formula in those cells but if you put a formula in the upper cells checking the lower cells and in the lower checking the upper you will get a circular reference error.
I recommend you just change the column calculating the % to check both:

=LET(status,CONCAT(FILTER($K$6:$K$17,$J$6:$J$17=J6)),
          IF(ISNUMBER(SEARCH("Done",status)),1,
              IF(ISNUMBER(SEARCH("RS",status))+ISNUMBER(SEARCH("LS",status)),0.5,
                   "")))