+ Reply to Thread
Results 1 to 15 of 15

problem using conditional formatting to flag cells by color and count them

  1. #1
    Registered User
    Join Date
    09-08-2011
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    Question problem using conditional formatting to flag cells by color and count them

    Any help would be greatly appreciated! I am using Excel 2003 and am not sure how to use the conditional formatting. I have attached an example of the workbook I have, I'm sorry I could not attach the actual one b/c it contains patient information, however this one is set up just as I have for now.
    What I don't know how to do is color the In Date box a certain color if there is no completion date entered w/in a certain number of days.
    So if there is no COM DATE entered 10 days after the IN DATE I would like the IN DATE cell to turn yellow.
    If no COM DATE entered w/in 20 days after the IN DATE - orange
    no COM DATE w/in 30 days - red.

    After the IN DATE cells have been colored I am wanting to count the number of cells w/that color.

    I hope I have explained this appropriately and that the attachment helps.
    Thanks in advance for any help!
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: problem using conditional formatting to flag cells by color and count them

    Select column E and change Conditional format setting to the attached.
    Attached Images Attached Images
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: problem using conditional formatting to flag cells by color and count them

    Perhaps using a helper column, so you can count the colours after that. You can then hide this helper column - see attached.

    Otherwise, you will have to settle to using VBA for the counting part.
    Attached Files Attached Files
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  4. #4
    Registered User
    Join Date
    09-08-2011
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: problem using conditional formatting to flag cells by color and count them

    Thank you for the reply! It's been extremely helpful I would have never gotten that I don't believe. I used the conditional formatting given and I have attached a sample of the issues I am having now. I don't quite understand the formula that was used and therefore I don't know the fix to this one. Thank you very much for the help.
    Attached Files Attached Files

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: problem using conditional formatting to flag cells by color and count them

    That is because your formulas are offset by one cell.

    Select all of column E, by click the E at the top of the column, then invoke Conditional formatting and change all the E2 and F2 references to E1 and F1, respectively.

  6. #6
    Registered User
    Join Date
    09-08-2011
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: problem using conditional formatting to flag cells by color and count them

    So with the helper column would I need to go through and set each cell up individually? Cause it does not look like it's done through formatting, is that correct?

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: problem using conditional formatting to flag cells by color and count them

    Not sure what you are asking.. did the conditional formatting (without the helper column) that I offered not work?

    With the helper column that ron offered, you are entering the formula in G2 and copying it down the column, then, yes, conditional formatting is applied still to column E and is simply checking what number is in the helper to determine which colour to use.

  8. #8
    Registered User
    Join Date
    09-08-2011
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: problem using conditional formatting to flag cells by color and count them

    I'm sorry I wasn't very clear. The conditional format you provided me with works like a charm =) THANK YOU! I was referring to what Ron has posted, and I'm going to be trying to work on that today.
    Is there a way that these numbers (the number of yellows, oranges, and reds) could be compiled onto a seperate sheet and listed by provider?

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: problem using conditional formatting to flag cells by color and count them

    See attached.

    In each sheet, in I2 to count Yellow:

    Please Login or Register  to view this content.
    in I3 to count Orange:

    Please Login or Register  to view this content.
    and in I4 to count Red:

    Please Login or Register  to view this content.
    adjust the ranges to suit.. No whole column references.

    Then in summary sheet, list the sheet names in A2 down and in B2, for Yellow:

    Please Login or Register  to view this content.
    copied down

    in C2, For Orange:

    Please Login or Register  to view this content.
    copied down

    in D2, For Red:

    Please Login or Register  to view this content.
    copied down
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-08-2011
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: problem using conditional formatting to flag cells by color and count them

    I'm sorry, but I did not see a reply button or "quick reply" this time so I'm posting this way. But thank you so much for your help! I have attached the example. There are notes on the summary page.
    I am wanting to see if there is a way to count "complient" charts to have it placed onto the summary sheet as the other numbers are. Thank you so much once again =)
    Attached Files Attached Files

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: problem using conditional formatting to flag cells by color and count them

    Try this formula in I5 of each sheet:

    Please Login or Register  to view this content.
    which is essentially the sum of all IN DATES with no COM DATES minus the Count of RED as in I4 of each sheet.

    The #VALUE error in the Narula sheet was caused by a typo you had in cell E13. You have date entered as 106/11 instead of 10/6/11

  12. #12
    Registered User
    Join Date
    09-08-2011
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: problem using conditional formatting to flag cells by color and count them

    Thank you soooo much! It worked out =) Would you be able to take a look at this and see how can the percentage be calculated correctly when there is a zero in the total column on the summary page?
    Attached Files Attached Files

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: problem using conditional formatting to flag cells by color and count them

    Sorry, where are we looking?

  14. #14
    Registered User
    Join Date
    09-08-2011
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: problem using conditional formatting to flag cells by color and count them

    I honestly can not remember what I was referring too on that last question, but if we can completely forget that I asked that I have another one. On the attachment I hope it helps explain what I'm asking for help on. The IN DATE cells are being flagged, but now I'm needing to keep the cells flagged if the COM DATE is 30 days after the IN DATE so that the deliquent/complient percentages are correct. I'm sorry, surely you are getting tired of me! Thanks so much for all of your genius =)
    Attached Files Attached Files

  15. #15
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: problem using conditional formatting to flag cells by color and count them

    See attached.

    Conditional formatting formula for column F:

    =AND(E1<>"",F1<>"",F1>E1+30)

    Formula in I4 adjusted to:

    Please Login or Register  to view this content.
    Formula in I5 adjusted to:

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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