Results 1 to 3 of 3

Thread: Conditional Formatting

  1. #1

    Conditional Formatting



    Register for a FREE account, and/
    or Log in to avoid these ads!

    In the attached spreadsheet, I already have conditional formatting for two of the tables (left and middle). On the right, I want to have a visual depiction of the data on the other two tables. The numbers are referencing a code for a training program and are in time order (first to complete on top, then last on bottom). What I want is:

    For AG5:AR77 I want it to turn green if the name in AG2 (in large text on top) has an x in either of the tables to the left. For example, if Mathisen has 2150 with an X, I want all the 2150's on the right most table to turn green, but only when Mathisen is in AG2.

    The only way I can think of doing this is manually putting conditional formatting in each individual cell with an AND forumla. Thoughts?

    Excel Trial 2.xlsx

  2. #2
    I figured it out. Had to do a COUNTIFS and retun the value into another cell, then use that for the conditional formatting. Seems pretty stable, since the data won't be changing, just the x's. Let me know if you can think of a better way!

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    You could do it in CF with a formula of

    =(COUNTIFS($B$3:$B$56,AG4,INDEX($C$3:$O$56,0,MATCH($AG$2,$C$2:$O$2,0)),"x")+COUNTIFS($R$3:$R$56,AG4,INDEX($S$3:$AE$56,0,MATCH($AG$2,$S$2:$AE$2,0)),"x"))>0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •