+ Post New Thread
Results 1 to 4 of 4
Office Software Thread, Excel 2013 - Conditional Formatting per row in Technical; Hi, I'm trying to make it easier for the staff who are monitoring staff attendance to check if people are ...
  1. #1
    linkazoid's Avatar
    Join Date
    May 2007
    Location
    London
    Posts
    633
    Thank Post
    111
    Thanked 88 Times in 72 Posts
    Rep Power
    78

    Excel 2013 - Conditional Formatting per row

    Hi,


    I'm trying to make it easier for the staff who are monitoring staff attendance to check if people are late.

    I have an Excel sheet with Staff names and their start time. I want to apply conditional formatting to highlight if they are on time or late. Any conditional formatting that I apply always seems to reference back to C2. I've tried to apply the formatting and copy and fill the formatting values but this didn't work either. Is anyone able to work some excel magic on the attached worksheet.

    Thanks

    Michael
    Attached Files Attached Files

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    Quote Originally Posted by linkazoid View Post
    Hi,


    I'm trying to make it easier for the staff who are monitoring staff attendance to check if people are late.

    I have an Excel sheet with Staff names and their start time. I want to apply conditional formatting to highlight if they are on time or late. Any conditional formatting that I apply always seems to reference back to C2. I've tried to apply the formatting and copy and fill the formatting values but this didn't work either. Is anyone able to work some excel magic on the attached worksheet.

    Thanks

    Michael
    You need to remove the absolute references and make them relative; just remove the $ before the row number.

    e.g. if you're formatting data in column D based on a value in column C, such as if d > C, starting at row 2 your conditional format would be ">$C2" This tells Excel always in column C (the $ before the column), but relative to row 2

    Example attached might make more sense
    Attached Files Attached Files

  3. Thanks to LosOjos from:

    linkazoid (23rd October 2013)

  4. #3
    linkazoid's Avatar
    Join Date
    May 2007
    Location
    London
    Posts
    633
    Thank Post
    111
    Thanked 88 Times in 72 Posts
    Rep Power
    78
    I can't see any obvious difference in you sheet, compared to my original sheet (Different to the uploaded one) but yours seems to work!
    Is it possible to add it to turn green if early or on time?

    Thanks,

    Michael

  5. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    Quote Originally Posted by linkazoid View Post
    I can't see any obvious difference in you sheet, compared to my original sheet (Different to the uploaded one) but yours seems to work!
    Is it possible to add it to turn green if early or on time?

    Thanks,

    Michael
    Difference is like I said - you need to remove the lock on row (the $ symbol). If you check your sheet, the formula was ">$C$2", whereas it needed to be ">$C2" (notice the second $ is gone)

    To make a condition that looks if they arrive on time, it'd be "=$C2", or early "<$C2" or even on time or earlier "<=$C2"

  6. Thanks to LosOjos from:

    linkazoid (23rd October 2013)

SHARE:
+ Post New Thread

Similar Threads

  1. Excel 2003 Conditional Formatting - Student Targets
    By j17sparky in forum Office Software
    Replies: 18
    Last Post: 21st June 2010, 01:49 PM
  2. [MS Office - 2007] Excel formulas and formatting not working
    By reggiep in forum Office Software
    Replies: 2
    Last Post: 6th October 2009, 02:21 PM
  3. [MS Office - 2003] Excel - Conditional Formatting using formula
    By park_bench in forum Office Software
    Replies: 1
    Last Post: 10th June 2009, 02:25 PM
  4. Conditional formatting help needed
    By Jobos in forum How do you do....it?
    Replies: 3
    Last Post: 3rd June 2008, 01:58 PM
  5. Help with conditional formatting
    By randle in forum Windows
    Replies: 3
    Last Post: 25th October 2007, 01:20 PM

Thread Information

Users Browsing this Thread

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

Posting Permissions

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