Forum Discussion

Millertime9460's avatar
Millertime9460
Copper Contributor
Nov 30, 2022
Solved

Conditional Formatting

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
}

 

Formulas and Functions
  • mtarler's avatar
    mtarler
    Nov 30, 2022

    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. 

6 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Nov 30, 2022

    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. 

    • Millertime9460's avatar
      Millertime9460
      Copper Contributor
      Nov 30, 2022
      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."
      • mtarler's avatar
        mtarler
        Silver Contributor
        Nov 30, 2022

        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.

         

         

Resources