Results 1 to 5 of 5

Thread: Highlight all jobs belonging to one client
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Highlight all jobs belonging to one client

    I have a spreadsheet with a list of jobs and the client.
    Any job that has an order has an expiry date of 10 years, anything else has an expiry date of 1 year.
    Any job that is still in date has been highlighted Green using conditional formatting.

    I would like to highlight all rows with the same client name if that client has at least 1 entry which is an order (so that the order overrides the expiry date). I have been racking my brains all day over this so any help would be much appreciated. I'm only a basic user of excel so I do apologise if i have missed something rather simple.

  2. #2
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Highlight all jobs belonging to one client

    Oops! What I posted doesn't work.
    Last edited by thisoldman; May 12th, 2018 at 08:41 AM.

  3. #3
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Highlight all jobs belonging to one client

    Try this.

    A B
    1 Company Order date
    2 Alpha 02/01/2006
    3 Beta 07/01/2010
    4 Gamma 02/01/2009
    5 Alpha 02/01/2007
    6 Beta 02/01/2007

    Conditional formatting formula applied to A2:B6.

    =COUNT(1/(($A2=$A$2:$A$6)*($B$2:$B$6>=DATE(YEAR(TODAY())-10,MONTH(TODAY()),DAY(TODAY())))))
    Last edited by thisoldman; May 12th, 2018 at 09:28 AM.

  4. #4
    New Member
    Join Date
    May 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight all jobs belonging to one client

    My spreadsheet currently looks something like:

    A B C D E F G H
    1 Organisation Subject Status State Invoiced End Date Expiry Date In Date
    2 Client A Job A Complete Quotation No 17/09/2015 17/09/2016 TRUE
    3 Client A Job B Complete Order Yes 24/09/2015 24/09/2025 FALSE
    4 Client A Job C Complete Order Yes 12/10/2015 12/10/2025 FALSE
    5 Client A Job D Complete Quotation No 25/10/2015 25/10/2016 TRUE
    6 Client A Job E Complete Quotation No 04/11/2015 04/11/2016 TRUE
    7 Client A Job F Complete Order Yes 16/11/2015 16/11/2025 FALSE

    I have used conditional formatting to highlight the row dependant on the value of Column H.
    If H = True then the row is Green. If H = FALSE then the row is RED.

    I need the TRUE values to override the FALSE values if the value in Column A is the same.

    Hopefully that explains what i need a bit better

  5. #5
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Highlight all jobs belonging to one client

    Rules apply to $A$2:$H$7

    Order of the rules, top to bottom, as they appear in the 'Conditional Formatting Manager':

    Green row if the Organization has TRUE in any record:: =MATCH($A2&TRUE(),$A$2:$A$7&$H$2:$H$7,0)
    Red row when column H is false:: =$H2=FALSE
    Green row when column H is TRUE formula:: =$H2
    Last edited by thisoldman; May 12th, 2018 at 07:29 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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