# Using Multi Conditional formats for a calendar

1. ## 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

2. ## Re: Using Multi Conditional formats for a calendar

What if there are multiples of the date and they have differing codes, times, etc.?

3. ## 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.

4. ## 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

5. ## 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. ## 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. ## 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. ## 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

9. ## 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. ## 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. ## Re: Using Multi Conditional formats for a calendar

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

12. ## 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. ## 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"

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1