+ Reply to Thread
Results 1 to 7 of 7

Complicated Conditional Formatting based on 2 Tables

  1. #1
    Registered User
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    36

    Complicated Conditional Formatting based on 2 Tables

    This seems way too complicated for me so I need a little help.
    I have two separate tables that have data copied and pasted into them from different sources.
    The first table (MONITORDATA) has a lot of outputs, date, time, memory number, and product number. The second table (HISTORY) has Date Time (as one entry), contents, memory number, ID, and Mode.

    Is there anyway I could highlight an entire row in MONITORDATA when the HISTORY[MODE] = "ALM" BUT, only if HISTORY[MEMORY] = MONITOR DATA[MEMORY, the times are also equal. The time is what I'm really having trouble with because it also has a date in the same cell.

    I was thinking that maybe I could copy the contents of HISTORY[DATE TIME] to another column but strip the date from the value.

    I would really love some help on this. Thank you!

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Complicated Conditional Formatting based on 2 Tables

    Can you upload an example of how your data is laid out? (Go Advanced>Manage Attachments)

    I don't think you can use Table column references in Conditional Formatting (I may be wrong about that)
    But you should be able to just by referencing columns.
    To just look at the time, in history
    =MOD(A2,1) will remove the date in your formula.
    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

  3. #3
    Registered User
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    36

    Re: Complicated Conditional Formatting based on 2 Tables

    MonTemplate.xlsx

    Here is a document. Sheet 1 has the history data in it. I figured out how to make a separate column with only the time but nothing else really. Let me know if you can help!

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Complicated Conditional Formatting based on 2 Tables

    Okay, this is better. Now,
    Is there anyway I could highlight an entire row in MONITORDATA when the HISTORY[MODE] = "ALM" BUT, only if HISTORY[MEMORY] = MONITOR DATA[MEMORY, the times are also equal.
    Table 1 (MonitorData) has 109 rows and 2 (History) has 106. Would they normally have the same # of rows?

    Are you looking at Cycle Time as one of the times to compare? If not, then what? Cycle time is not a real time, you'll need to convert them.
    There is no "Memory" header on your MonitorData Table.

  5. #5
    Registered User
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    36

    Re: Complicated Conditional Formatting based on 2 Tables

    Sorry about that, I have hidden quite a few coulumns from the data ouputs. The Time column is hidden, I forgot to mention that, apologies!

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Complicated Conditional Formatting based on 2 Tables

    Okay, I found the time column (DB). There is still no MEMORY column so don't know how I can compare the two MEMORY Columns.
    Also, your time in HISTORY is text. To fix this, change your formula to =RIGHT(A5,5)+0

    The CONDITIONAL Formatting Formula you are looking for (I could do better if I could find a MEMORY Column I am assuming it is De ("Memo #") is

    Select your entire Table 1 (no headers), Conditional formatting> New rule

    =AND(Sheet1!$E5="ALM", Sheet1!$F5=$DB7, $DE7 = Sheet1!$C5)

  7. #7
    Registered User
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    36

    Re: Complicated Conditional Formatting based on 2 Tables

    That is the memory column, sorry! But I think I can figure it out from what you've given me. Thank you!

+ 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. Complicated Conditional Formatting - Help!!
    By ncindasun in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-14-2014, 05:27 AM
  2. [SOLVED] Complicated conditional formatting with multiple columns
    By jedidavidalan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2014, 05:09 AM
  3. Conditional Formatting with formula
    By christlivethinme in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-28-2012, 02:30 AM
  4. Complicated conditional formatting problem.
    By hodges5378 in forum Excel General
    Replies: 2
    Last Post: 04-23-2008, 04:28 PM
  5. 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