Conditional formating

[joelrjt]

Hi there,

I am trying to create a Tracker in which three conditions have to be met.

If a complaint comes in and it is completed on 1st day, Row goes Green

If a complaint comes in and is between 2-14 days, Row goes Amber

If Complaint is over 21 Days Old, Row goes Red

How do I set this up, what would be the best way to do it using Conditional Formatting

My Excel Sheet is attached.

Also what is the best way to be able to get All Agents on Tab Agent to be populated as drop down menu in Sheet 2

Thanks

[Tony Valko]

What should the count of days be based on? The Date Received or the Date Acknowledged?

For the drop down list...

Create a list of the unique agent names. In your file on the Agent sheet, Andrea Bouckley is the only name listed more than once.

Maybe this...

On the Agent sheet select the range B3:B21

In the Name box (that little "box" where it shows the cell address of the selected cell) type in a name for the selection of cells. Something like Agents.

Then, on your other sheet setup the drop down list and use the named range as the source.

Data>Validation
Allow: List
Source: =Agents
OK out

[joelrjt]

The Condition is based on the Date Acknowledge

Thanks

[joelrjt]

Is this the same as well for Excel 2003

Thanks

[judgeh59]

so to get this to work you need to highlight the entire row (even a couple of them)...take a look at the conditional formating formulas....I did the data validation but I'm on 2010 and I got a compatibility error for 2003 so it may not work....I don't have 2003 to test on...sorry....HTH

[joelrjt]

Thank you for your help, much appreciated

[joelrjt]

One last thing, how do I call data from Agent tab on Sheet 2

So that Agent sits behind Sheet 2 but is the front page.

Thanks

[Tony Valko]

In Excel 2003 (and earlier versions) you can't directly refer to another sheet when using data validation. So, that's why we use a named range as a work around.

Watch this 5 min video on Youtube.

How to setup a data validation drop down list

Excel versions 2003 and earlier

http://youtube.com/watch?v=t2OsWJijrOM

[joelrjt]

I'm looking to now add a few things

If the Date Acknowledged(C2) is greater then

Same Day row goes green
7-14 Days Row goes Amber
21 Days the row goes red

This will probably work like todays date is equal to date acknowledged or greater then todays date or words to that effect.

Secondly how do I now get the total number of greens, reds and ambers so I know how many are within SLA each month.

Thanks.

[joelrjt]

I'm looking to now add a few things

As soon as a date is entered, the Date Acknowledged(C2) would be:

Same Day row goes green
7-14 Days Row goes Amber
21 Days the row goes red

This will probably work like todays date is equal to date acknowledged or greater then todays date or words to that effect.

Secondly how do I now get the total number of greens, reds and ambers so I know how many are within SLA each month.

Thanks.