Using Multi Conditional formats for a calendar

**Coldsteel** · 09-13-2010, 12:08 PM

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

**NBVC** · 09-13-2010, 12:58 PM

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

**Palmetto** · 09-13-2010, 01:30 PM

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.

**Coldsteel** · 09-13-2010, 05:51 PM

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

**Palmetto** · 09-13-2010, 09:27 PM

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)

**Coldsteel** · 09-14-2010, 03:16 AM

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

**Palmetto** · 09-14-2010, 07:20 AM

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.

**Coldsteel** · 09-14-2010, 05:23 PM

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

**Palmetto** · 09-14-2010, 06:04 PM

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.

**Coldsteel** · 09-15-2010, 02:24 AM

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

**Coldsteel** · 09-15-2010, 11:54 PM

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

**Palmetto** · 09-16-2010, 07:07 AM

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.

**Palmetto** · 09-16-2010, 04:23 PM

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"

Using Multi Conditional formats for a calendar

LinkBack

Thread Tools

Rate This Thread

Display

Using Multi Conditional formats for a calendar

Re: Using Multi Conditional formats for a calendar

Re: Using Multi Conditional formats for a calendar

Re: Using Multi Conditional formats for a calendar

Re: Using Multi Conditional formats for a calendar

Re: Using Multi Conditional formats for a calendar

Re: Using Multi Conditional formats for a calendar

Re: Using Multi Conditional formats for a calendar

Re: Using Multi Conditional formats for a calendar

Re: Using Multi Conditional formats for a calendar

Re: Using Multi Conditional formats for a calendar

Re: Using Multi Conditional formats for a calendar

Re: Using Multi Conditional formats for a calendar

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions