Results 1 to 5 of 5

Thread: Conditional Format Cell Based on 'No Match' in Another Range(row).

  1. #1
    Junior Member
    Join Date
    Aug 2012
    Posts
    13
    Rep Power
    0

    Conditional Format Cell Based on 'No Match' in Another Range(row).

    Formatting Demo (OFF).xlsx
    The attached is an example of what the end result should look like but I'm not sure what kind of conditional formatting function would accomplish this.

    Essentially, Trainee 1 has a day off during the week, so there's no alert in the last column of that row (I).
    Trainee 2 has no days off during the week, so there should be an alert in column (I) for that row, 'Not OFF.'

    I've seen plenty of methods for dealing with similar functions between columns, but nothing exact and nothing for rows.

    In the final version of this form, if the word 'OFF' is typed into the bottom cell of the row, it's also placed in the top. This means that the formula doesn't need a preference of where to look to find value.

    Let me know if you have any questions with this or if there's just no way this will function.

    Thanks in advance for the help!

  2. #2
    Junior Member
    Join Date
    Dec 2012
    Posts
    16
    Rep Power
    0
    Hi Orestees

    May be using the IF & LOOKUP, see the attached file. You need to stay clear of merging cells, they are the work of the devil!
    Attached Files Attached Files

  3. #3
    Junior Member
    Join Date
    Aug 2012
    Posts
    13
    Rep Power
    0
    Thanks for taking a look Kevin! I tried the formula in my working version but for some reason the LOOKUP wasn't working. Due to your help though, I reworked the formula to =IF(OR(B3="OFF", C3="OFF", D3="OFF", E3="OFF", F3="OFF", G3="OFF"),"OFF","NOT OFF") and that seemed to fix it. It's not as pretty but this one worked.

    The only thing I can think that caused an issue is that the cells containing "OFF" are actually formulas themselves. I forgot to add this into the demo, my bad.

    I've reattached the demo with the formula just in case someone else finds a use for it. It also has the formulas that create the "OFF" designator along with formatting. Formatting Demo (OFF).xlsx

    Thanks again Kevin!

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    This would be much simpler

    =IF(COUNTIF(B3:G3,"OFF"),"OFF","NOT OFF")
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Aug 2012
    Posts
    13
    Rep Power
    0
    Yes it is! That looks much cleaner than the prior one. Kevin's was very much like it but I think due to the way the equations worked, it couldn't coincide. Thanks to the both of you for the help!! Greatly appreciated!

Similar Threads

  1. Replies: 4
    Last Post: 06-01-2013, 01:08 PM
  2. Replies: 2
    Last Post: 01-24-2013, 09:03 PM
  3. Autofill the data based on non blank cell in next row?
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 3
    Last Post: 11-29-2012, 04:16 PM
  4. Conditional Format Based On Percentage Variance
    By srizki in forum Excel Help
    Replies: 3
    Last Post: 10-09-2012, 03:28 AM
  5. Replies: 4
    Last Post: 07-27-2012, 08:43 AM

Posting Permissions

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