+ Reply to Thread
Results 1 to 15 of 15

Conditional Formatting with a twist

  1. #1
    Registered User
    Join Date
    01-02-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    11

    Lightbulb Conditional Formatting with a twist

    Hi there,

    I have a roster where i want to implement conditional formatting.

    If you see my roster below, you will see the green and red fields.* When any number up to 8 is entered in the "Normal Hours" column, I want it to be GREEN and if above that, i want it to be ORANGE. And for the Overtime hours column i want it to automatically go RED when any number is entered.*
    For the TOTAL HOURS box, i want to use the Icon set and utilise the tick and apostrophe icons. When the total hours are '213' it's always a green tick but if it's less than 213 or more than 213 i want it to be a yellow apostrophe.

    Can someone assist with this setup please?


    roster.PNG

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,708

    Re: Conditional Formatting with a twist

    Hello DIKIC07. Welcome to the forum.

    Pics and screen shots don't help much ... if at all.

    Uploading a representative Excel file gives context and real data to work with.

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

    be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”
    The file name will appear at the bottom of your reply.
    Dave

  3. #3
    Registered User
    Join Date
    01-02-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Conditional Formatting with a twist

    FlameRetired

    Re: Conditional Formatting with a twist
    Hello DIKIC07. Welcome to the forum.

    Pics and screen shots don't help much ... if at all.

    Uploading a representative Excel file gives context and real data to work with.

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

    be sure to desensitize the data

    click “Go Advanced” (next to Post Quick Reply – bottom right),
    scroll down until you see “Manage Attachments”, click that,
    click “Browse”.
    select your file(s)
    click “Upload”
    click “Close window”
    click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Hi FlameRetired,

    Thank you for a swift response, it's much appreciated.

    I have included the excel template without sensative information to response.
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,708

    Re: Conditional Formatting with a twist

    Some clarification is needed.

    But first:
    As a new member you need to familiarize yourself with our forum rules. There aren't many of them, but one of them is:

    Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.
    For normal conversational replies, try using the QUICK REPLY box below.

    Next:
    You mention Normal Hours column. I see no such column. If you are not aware of it columns are arranged vertically and rows horizontally.

    Having said that I see NORMAL HOURS as row labels in column C just under SHIFT TIMES. Occasionally it's HOURS WORKED. Is there a difference when considering
    When any number up to 8 is entered in the "Normal Hours" column, I want it to be GREEN and if above that, i want it to be ORANGE.
    Do you want HOURS WORKED included?

    I may have more questions as we go.

  5. #5
    Registered User
    Join Date
    01-02-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Conditional Formatting with a twist

    Apologies for that. I will go and read the rules so i understand the requirements. Thanks for the heads up.

    Yes, i made a mistake, its supposed to say row rather than column. And that's a good pickup. I need to remove 'Hours Worked' and replace it with 'Normal Hours'.

    Normal hours amount to 8 with GREEN background, anything more than 8 should make the background go to ORANGE to identify a problem.

    When overtime hours are added, the Overtime box background needs to go into RED.

    No problems about further clarifications - ask away .... I appreciate your help with this.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,708

    Re: Conditional Formatting with a twist

    You will want to stay alert for any extra spaces in the data ... there are some in the row labels. They cause havoc in CF formulas. I cleaned up my copy at this end.

    Do you want same rules to apply to the AD-HOC SECURITY section?

    If so:
    There are merged cells in that section. They need to be un-merged. In addition to causing formula havoc they do not allow format painting.
    The row labels in that section are not consistent with the other sections. (There is a COMMENTS row.) May those be changed accordingly? Currently there are no labels:
    OVERTIME HOURS
    COVERAGE
    LEAVE/SHIFT TYPE

  7. #7
    Registered User
    Join Date
    01-02-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    11
    There is no need for these amendments in the AD-HOC security section.
    That section has no regulations.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,708

    Re: Conditional Formatting with a twist

    Thanks.

    Just got up. Am back to it.

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,708

    Re: Conditional Formatting with a twist

    In the attached I tried to set the applies to range D8:J109.

    Initially it worked, but when I saw the need to clear out existing formats the applies to automatically reset to non contiguous ranges.

    The formulas are Normal hours > 8
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Normal hours > 0 and <= 8
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and Overtime hours
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I am not familiar with 'Tick' and 'apostrophe' icons. I also didn't see a way to set the criteria as described.

    The extra spaces in column C are cleaned up and the labels standardized.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-02-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Conditional Formatting with a twist

    FlameRetired, thank you very much for that. That will help me minimise mistakes when checking over the hours.

    In regards to the tick and apostrophe i was talking about, if you look under Conditional Formatting > Icon Sets ... its there ... cells K55/56 and K110/111 have the total for the week. I wanted a green tick to appear if the total is 213 and an orange apostrophe if its anything under or over 213 ...

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,708

    Re: Conditional Formatting with a twist

    I don't see tick marks where you indicate.

    Also the nature of the criteria may not permit those as anything under or over 213 is essentially a <> 213 formula. Icons are not available in the 'Use formula to determine format' option that I've ever known. I also don't find an apostrophe (') in the icon set. By chance do you mean (!)?

    I tried typing <> 213 and = 213 into the rule 'Format all cells based on their value'. Format Manager seemed to accept it, but nothing shows in K55/56 or K110/111. Upon further investigation Format Manager changed one of those formulas to text. I also un-merged those cells. It didn't help either.

    Edit This one has me talking to myself. I've called for community help.
    Last edited by FlameRetired; 01-04-2019 at 12:03 AM.

  12. #12
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,332

    Re: Conditional Formatting with a twist

    CF done for all cells of TOTAL HOURS WORKED. Values in rows 9, 15, 21 are changed to show working in TOTAL HOURS WORKED cells.
    > 213---> Yellow apostrophe
    =213----> Green Tick
    < 213---> Yellow apostrophe
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 01-04-2019 at 02:51 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  13. #13
    Registered User
    Join Date
    01-02-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Conditional Formatting with a twist

    FlameRetired, thank you very much for that. That will help me minimise mistakes when checking over the hours.

    In regards to the tick and apostrophe (my mistake, was meant to say exclamation mark) i was talking about, if you look under Conditional Formatting > Icon Sets ... its there ... cells K55/56 and K110/111 have the total for the week. I wanted a green tick to appear if the total is 213 and an orange exclamation mark if its anything under or over 213 ... kvsrinivasamurthy has done it but for the wrong total hours. I don't need it per person, just the total hours for the week, for everyone - cells K55/56 and K110/111.

    Interesting how you did that kvsrinivasamurthy - i could hsve sworn i tried that and it wasn't working. I must have been doing something wrong. And thank you for your contribution kvsrinivasamurthy.

  14. #14
    Registered User
    Join Date
    01-02-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Conditional Formatting with a twist

    Quote Originally Posted by kvsrinivasamurthy View Post
    CF done for all cells of TOTAL HOURS WORKED. Values in rows 9, 15, 21 are changed to show working in TOTAL HOURS WORKED cells.
    > 213---> Yellow apostrophe
    =213----> Green Tick
    < 213---> Yellow apostrophe
    kvsrinivasamurthy i applied your method into cells K55/56 and K110/111 and it works perfectly. Great job. Thank you.

  15. #15
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,332

    Re: Conditional Formatting with a twist

    Welcome. Happy to here you are satisfied.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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