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 ...
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. 

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.

 

 

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

    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%.

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

      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,
                   "")))

Resources