+ Reply to Thread
Results 1 to 13 of 13

Using Multi Conditional formats for a calendar

  1. #1
    Registered User
    Join Date
    06-21-2010
    Location
    Texas, San Antonio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Using Multi Conditional formats for a calendar

    Hello all,

    I have a excel sheet which allows me to select a rep in cell G2 and displays all the selected reps info in sheet2. I have a excel calendar in Sheet3. I have a legend in Sheet2 with diffenert colors to represent different codes the reps have used that day. What I want to do is make conditional formatting rules to use the different colors in my legend.

    For example:

    If I select a rep: and on 9/1/10 he had a T for Tardy
    I want the color Orange to show on 9/1 on the calendar.
    If they have times greater than 5 minutes then I want the color to show

    I have tried to make formulas but I have had little success. I tied to use a named range in Sheet2 to try to help me but all I can do is find the dates a rep used.

    Here is my formula:

    =NOT(ISERROR(MATCH(A3,RepDates,0)))


    Thanks
    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,902

    Re: Using Multi Conditional formats for a calendar

    What if there are multiples of the date and they have differing codes, times, etc.?
    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
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Using Multi Conditional formats for a calendar

    There are no time values in your workbook so it is not possible to determine "times greater than 5 minutes".


    Conditional Formatting does not allow the direct referencing of cells on other worksheets, but this can be overcome using named ranges.

    This is best accomplished using a two helper cells in columns I and J ( or your choice)
    First, on Sheet1, starting in I2 ( or any other column) enter this formula and copy it down the column

    =A2&B2, to create a unique look up value

    Note: you have duplicate dates with multiple codes such as Jason, 9/22/10 with codes V and F. You can only match the first date/code as conditional formatting only handles a single true or false value. You could however, order the precedence of the Conditional Formats if there is a preference for showing "F" over "V".

    In J2: =C2

    Create a dynamic named range for this look up table then reference the named range in a look up formula used in the Conditional Formatting:

    =VLOOKUP(A3&$A$1,Lookup_table,2,FALSE)="V"
    =VLOOKUP(A3&$A$1,Lookup_table,2,FALSE)="FMLA"
    =VLOOKUP(A3&$A$1,Lookup_table,2,FALSE)="T"
    =VLOOKUP(A3&$A$1,Lookup_table,2,FALSE)="F"
    =VLOOKUP(A3&$A$1,Lookup_table,2,FALSE)="S"
    =VLOOKUP(A3&$A$1,Lookup_table,2,FALSE)="J"

    Starting with cell A3 on Sheet3, select all cells in the calendar and apply the Conditional Formats/formulas.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  4. #4
    Registered User
    Join Date
    06-21-2010
    Location
    Texas, San Antonio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Using Multi Conditional formats for a calendar

    Thanks everyone for the help.

    I was trying to apply the updates to my excel sheets but I am not able to get them to work. I named my table Lookup_table in Sheet 1 and added the formulas to columns I and H. I also added a field to help me count how many times a rep may go over 5 minutes. I know I am applying something incorrectly I just can't put my finger as to what area I am not applying correctly.

    Here is my updated Worksheet

    Thanks again
    Attached Files Attached Files

  5. #5
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Using Multi Conditional formats for a calendar

    The two helper columns were not intended to be come a part of your normal data table, though it doesn't hurt anything that they are. However, the named range you created should only include columns I and J, not A thru J.

    Change the column references to $I$1:$J$25, or if you expect this table will grow you can create a dynamic named range using this formula:

    =OFFSET(Sheet1!$I$1,0,0,COUNTA(Sheet1!$I:$I),2)

  6. #6
    Registered User
    Join Date
    06-21-2010
    Location
    Texas, San Antonio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Using Multi Conditional formats for a calendar

    Thanks for again for the help. It is working great now. I did have one question, how can I make order the precedence in the conditional formats.

    Thanks

  7. #7
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Using Multi Conditional formats for a calendar

    In Excel 2007+, the CF Rules Manager has a couple of up/down arrow buttons situated next to the Delete Rule button.

    Select the format condition you want to move then use the arrows to sequence per your needs.

  8. #8
    Registered User
    Join Date
    06-21-2010
    Location
    Texas, San Antonio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Using Multi Conditional formats for a calendar

    I wanted to thank you again I am learning so much from this. I found the area to setup the sequence for the conditional formats. I am trying to implement the sequence, but I am not having any success.
    Here is the order I have placed in for my Conditional Formatting Sequence:

    =VLOOKUP(A3&$A$1,Lookup_table,2,FALSE)="O"
    =VLOOKUP(A3&$A$1,Lookup_table,2,FALSE)="T"
    =VLOOKUP(A3&$A$1,Lookup_table,2,FALSE)="FMLA"
    =VLOOKUP(A3&$A$1,Lookup_table,2,FALSE)="S"
    =VLOOKUP(A3&$A$1,Lookup_table,2,FALSE)="V"

    Can you double check my worksheet to make sure I am doing it right? I have included it with this post.

    Thanks
    Attached Files Attached Files

  9. #9
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Using Multi Conditional formats for a calendar

    As far as I can tell, your CF is working correctly. I think you may have a misunderstanding about what I meant by sequencing the formats.

    This is not always necessary and I only made mention of it because your data showed two difference codes for the same date for Jason. Based on what I can tell about your workbook, this was probably an error because he will not have a vacation day and free day on the same date - it will be one or the other.

    If you are, in fact, having an issue, then be specific about your "lack of success" so that we can pinpoint where the help is needed.

  10. #10
    Registered User
    Join Date
    06-21-2010
    Location
    Texas, San Antonio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Using Multi Conditional formats for a calendar

    The specific area I am trying to work on is the conditional format for T and O. I want the vlookup for O to take precedence over the vlookup for T.

    For example if a rep is late a 3 min at the beginning of their shift on 9/30 I would key the data in the table in Sheet 1. Column J will have a T and the calendar will show a orange for the day. If the rep is late again for the same day coming from break, then I would key in another row of data in the for the same day and put a O instead of a T. I would like date 9/30 on the calendar would show up red instead.

    thanks

  11. #11
    Registered User
    Join Date
    06-21-2010
    Location
    Texas, San Antonio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Using Multi Conditional formats for a calendar

    Would the use of VBA help me with a sort order for the Conditional Formats?

  12. #12
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Using Multi Conditional formats for a calendar

    The problem is with multiple dates for the same individual. VLOOKUP stops at the first match.
    I don't have time at the moment to work on this, but it will require a different approach to match subsequent entries of the same date, probably using a COUNTIFS formula in conjunction with an INDEX/MATCH formula.

  13. #13
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Using Multi Conditional formats for a calendar

    The specific area I am trying to work on is the conditional format for T and O. I want the vlookup for O to take precedence over the vlookup for T
    Your formula for this condition shows an error

    =VLOOKUP(XFD3&$A$1,Lookup_table,2,FALSE)="O"

    It should read:
    =VLOOKUP(A3&$A$1,Lookup_table,2,FALSE)="O"

+ 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