+ Reply to Thread
Results 1 to 9 of 9

Conditional formatting if the complicated requirements are met

  1. 07-23-2015, 08:57 AM #1
    gesaugen
    gesaugen is offline
    Registered User
    Join Date
    02-09-2015
    Location
    Belgrade
    MS-Off Ver
    MS Office 2016
    Posts
    21

    Conditional formatting if the complicated requirements are met

    Hi all
    Can anyone help me with this problem:
    I have two worksheets
    In sheet1 i have columns: date1, ID number, name
    In sheet2 i have columns: date2, ID number, name

    To explain the purpose of sheets: in sheet1 i keep the records of people who had some kind of road accident. In sheet2 i keep records of people who attended safe driving education. People who had some kind of road accident should attend safe driving education (even if they attended it before the accident) so I would like to make a conditional formatting rule which will color column "name" in shee1 if the date2 is lesser than date1 for same ID number in both sheets. ID numbers in both sheets aren't in same order so person from sheet1, if exists in sheet2, isn't placed in same row in both sheets

    help please
    Reply With QuoteRegister To Reply
  2. 07-23-2015, 09:08 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,448

    Re: Conditional formatting if the complicated requirements are met

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  3. 07-23-2015, 09:21 AM #3
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Conditional formatting if the complicated requirements are met

    Assuming that the ID's only occur once on sheet2 (It gets MUCH more complex if that's not true), I made two rules

    =ISERROR(MATCH($B2,Sheet2!$B$2:$B$20,0)) turns the name cell red if the ID does not even appear on Sheet2

    =$A2>INDEX(Sheet2!$A$2:$A$20, MATCH(B2,Sheet2!$B$2:$B$20,0)) turns the name cell Green if the date on sheet1 is greater than the date on sheet2

    Modify the second formula if you want dates on sheet2 > dates on sheet1
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  4. 07-27-2015, 07:19 AM #4
    gesaugen
    gesaugen is offline
    Registered User
    Join Date
    02-09-2015
    Location
    Belgrade
    MS-Off Ver
    MS Office 2016
    Posts
    21

    Re: Conditional formatting if the complicated requirements are met

    example.xlsx

    Here's the example.
    I would like that whole row in "accident" sheet becomes colored if the "date" in accident sheet is greater than "date" in "education" sheet for same "ID number" from booth sheets
    Reply With QuoteRegister To Reply
  5. 07-27-2015, 10:17 AM #5
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Conditional formatting if the complicated requirements are met

    Selected A2:C20 and used this formula

    =MAX(INDEX(Education!$C$2:$C$6, INDEX((Education!$A$2:$A$6=$A8),)))<=$A2
    Does that work for you?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 07-28-2015, 03:33 AM #6
    gesaugen
    gesaugen is offline
    Registered User
    Join Date
    02-09-2015
    Location
    Belgrade
    MS-Off Ver
    MS Office 2016
    Posts
    21

    Re: Conditional formatting if the complicated requirements are met

    Hi ChemistB,
    Thanks for your suggestion but it doesn't work as it's related to fixed positions of values in cells. If you add in "accidents" sheet value "3.1.2015 124286 JOHN DOE", it doesn't get colored and it should cause it meets the required conditions

    I need formula that refers to whole column in the worksheet so that I can filter values for example by date and that they sill get formatted properly
    Reply With QuoteRegister To Reply
  7. 07-28-2015, 04:43 AM #7
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Conditional formatting if the complicated requirements are met

    Pl See attached file With Helper Column.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 07-28-2015, 06:48 AM #8
    gesaugen
    gesaugen is offline
    Registered User
    Join Date
    02-09-2015
    Location
    Belgrade
    MS-Off Ver
    MS Office 2016
    Posts
    21

    Re: Conditional formatting if the complicated requirements are met

    Hi kvsrinivasamurthy,
    I would like solution without "helper column". Also much simpler version with helper column would be that in it we are using vlookup which would, based on "ID Number" from accident sheet, return date from education sheet for that ID and then only to use conditional formatting with rule that date from accident sheet is compared with helper column from same sheet...

    any other suggestion?
    Reply With QuoteRegister To Reply
  9. 07-28-2015, 07:13 AM #9
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Conditional formatting if the complicated requirements are met

    Range A2:C6 is named as EduRng
    Formula for CF for A2
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Complicated Conditional Formatting based on 2 Tables
    By mtma in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-07-2014, 03:59 PM
  2. Complicated Conditional Formatting - Help!!
    By ncindasun in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-14-2014, 05:27 AM
  3. [SOLVED] Complicated conditional formatting with multiple columns
    By jedidavidalan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2014, 05:09 AM
  4. [SOLVED] Conditional Formatting leaving cell blank if doesn't meet requirements
    By Rhiannon25 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-11-2013, 11:55 PM
  5. Conditional Formatting with formula
    By christlivethinme in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-28-2012, 02:30 AM
  6. Complicated conditional formatting problem.
    By hodges5378 in forum Excel General
    Replies: 2
    Last Post: 04-23-2008, 04:28 PM
  7. 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1