+ Reply to Thread
Results 1 to 6 of 6

Complicated conditional formatting with multiple columns

  1. #1
    Registered User
    Join Date
    04-22-2014
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    13

    Complicated conditional formatting with multiple columns

    Hi everybody, this forum is awesome! I have what I think is a pretty complicated issue. At my job, we have a running and constantly changing list of jobs that we have to work on throughout the day. The job management system is really unwieldy and it’s often hard to tell what exactly we have to do today and what can wait until tomorrow. So a few times a day, I export the list to an excel doc, which I’m trying to set up with conditional formatting to highlight as green the stuff for today, ghost back what can wait, and leave white and blank the cells that don’t have any info in them. I'm sorry, I but the attachment system wasn't working for me, so I can't provide the example I've been working on. If someone can help me with the attachment, I can edit the post to add it.

    So far, I’ve managed to set up a formula in the conditional formatting that takes into account column C, but I need to take into account information in columns B, C, and D to determine if the job line should be highlighted or dimmed. Column B is the job's title and it is just text, column C is the Begin date, and column D is the due date. Hopefully all this makes sense so far, because here’s what I need the conditional formatting to do.

    If the begin date in column C is Today at 9:00am or earlier, and the due date in column D is today, I need to highlight the column. I managed to figure out the first part of this formula, but don’t know how to keep the line from hilighting if the due date is tomorrow: =$C2<=TODAY()+TIME(9,0,0)

    The second part is that I need to hilight jobs that begin and are due today (have today’s date in columns C and D), regardless of what time they have if the task title in column B starts with “Rework Request from PS” or “Rework Request from QA"

    Ideally, I’d like to change the text color to something nearly white to sort of hide the jobs that don’t need to be done today, and I figured that part out based on the formula I did manage: =$C2>=TODAY()+TIME(9,0,1)

    And finally, I’m hoping to apply this to the whole sheet so I can paste the report into it a few times a day without having to rewrite the formulas in a new sheet every time, and the number of items does change throughout the day. However when I do, the empty cells turn green as if they’re due today, and I can’t figure out how make them stay blank. I hope all of this makes sense. Please let me know if I need to clarify anything.

    Thanks in advance for your help, and for the mere existence of this forum!
    Last edited by jedidavidalan; 04-22-2014 at 02:39 PM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,173

    Re: Complicated conditional formatting with multiple columns

    =$C2<=TODAY()+TIME(9,0,0)
    that looks OK - should not highlight any date after today() at 9:00
    OR(Left (B2,22)="Rework Request from PS", left(B2,22)="Rework Request from QA")

    so to combine those

    AND( OR(Left ($B2,22)="Rework Request from PS", left($B2,22)="Rework Request from QA") , $C2<=TODAY()+TIME(9,0,0) )

    to stop the blank as that is seen as Less than today

    C2<>""

    AND( OR(Left ($B2,22)="Rework Request from PS", left($B2,22)="Rework Request from QA") , $C2<=TODAY()+TIME(9,0,0) , $C2<>"" )

    and the due date in column D is today,

    AND( OR(Left ($B2,22)="Rework Request from PS", left($B2,22)="Rework Request from QA") , $C2<=TODAY()+TIME(9,0,0) , $C2<>"", $D2=today() )

    =AND(OR(LEFT($B2,22)="Rework Request from PS",LEFT($B2,22)="Rework Request from QA"),$C2<=TODAY()+TIME(9,0,0),$C2<>"",$D2=TODAY())

    to upload a file

    go advanced
    Manage attachments
    add files
    basic uploader
    choose file
    upload
    Last edited by etaf; 04-22-2014 at 03:21 PM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    04-22-2014
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    13

    Re: Complicated conditional formatting with multiple columns

    Thanks for your quick response etaf. I'm sorry mine took so long, but I couldn't upload the file at work for some reason. But i've finally managed to bring the file home to upload it.

    I tried using the different steps of that formula, and it ends up just turning off all highlighting, not quite sure why. I finally managed to get my excel document to attach. I added a new separate conditional format to highlight the the QA and PS reworks, so that solved that. But I still need a way of preventing rows with an end date date of tomorrow or later in column D from highlighting, even if the begin date in column C takes place before today at 9:00.

    Also, is there a way to sort by what's highlighted?
    And, is there a formula for automatically counting how many cells in a column start with a specific phrase? I'd like to do an automatic count of how many of each kind job there is left.
    Attached Files Attached Files

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,173

    Re: Complicated conditional formatting with multiple columns

    Also, is there a way to sort by what's highlighted?
    yes you can sort by colour
    if you goto sort> custom sort> sort on > you can change from value to Font or Fill colour
    And, is there a formula for automatically counting how many cells in a column start with a specific phrase? I'd like to do an automatic count of how many of each kind job there is left.
    you can use a countif() or countifs()
    and use a left() to use only certain starting characters
    can you give a specific example of what your need

    you have a lot of conditional formatting now in that sheet
    to help me ,can you give some specific examples and what cells to use for the new conditional format
    as you say you have covered a few of the areas i mentioned - so not sure not what the rules are

  5. #5
    Registered User
    Join Date
    04-22-2014
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    13

    Re: Complicated conditional formatting with multiple columns

    That custom sort works brilliantly! I'm working on figuring out how to use the countif() now. My biggest problem is still preventing lines from highlighting if the due date in column D is "tomorrow" or later, or if the work status in column H isn't "Assigned." I think my general problem is i'm not sure I understand how to add secondary conditions to the formula. I know how to highlight a line based on the info in one column, but not two.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,173

    Re: Complicated conditional formatting with multiple columns

    try using an AND with some of your other conditions
    AND( $D2 <= today() , $H2 <> "assigned", the rule you are applying )

    the $ fixes the column so can then be applied across the range

    so for this rule ,
    =SEARCH("Rework Request from PS",$B2)

    =AND($D2 <= TODAY(), $H2 <> "assigned", SEARCH("Rework Request from PS",$B2) )

    Now the row has to be less than or equal today in D2 , must not have "assigned" in H2 and in B2 the text contains the string "Rework Request from PS"

    I have used
    =AND($D2 <= TODAY(), $H2 <> "assigned", SEARCH("Rework Request",$B2) )
    in the attached copy sheet of you spreadsheet
    Today's MRM List.txt (2)

    If you would write all your rules for each colour out here - perhaps using an AND or OR

    for example
    Date in D2 must Not be greater than today AND it must not have "assigned" in H2 AND B2 has the text "request" somewhere in the cell Cell I2 contains "fred" OR "Harry"

    hope that helps , and then I can review the sheet and help setup the conditions for you , at the moment you have quite a few conditions applying the same colour
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting with formula
    By christlivethinme in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-28-2012, 02:30 AM
  2. Complicated conditional formatting problem.
    By hodges5378 in forum Excel General
    Replies: 2
    Last Post: 04-23-2008, 04:28 PM
  3. Complicated conditional formatting if numbers match across a data set..How?
    By DrSues02 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-03-2005, 05:15 AM
  4. Replies: 1
    Last Post: 11-03-2005, 01:10 AM
  5. [SOLVED] Complicated VBA Conditional Formatting
    By Leslie in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 06-30-2005, 04:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1