Advanced conditional formatting? (Possibly challenging excel issue)

Hello all,

Below i have attached a sample of what i want to achieve through automating my report. I rank about 20 agents or so every week, then make a schedule for them. This sample reflects 30 minute time frames throughout the day. I need help automating some kind of indication identify what activity each scheduled agent will be doing at each given 30 minute time frame. Any help would be GREATLY appreciated!

Sample.xlsx

So how do you qualify who does what? by rank then by whats most important i.e. outside first, then office, then handling complaints, then doing nothing?

Originally Posted by Wyliecody

So how do you qualify who does what? by rank then by whats most important i.e. outside first, then office, then handling complaints, then doing nothing?

Thanks so much for the quick reply!
Priority follows:
1st-Needed outside
2nd- Needed In Office
3rd-Needed Handling Complaints
Last or Lowest - Needed doing nothing.

I should also mention that the names reflecting in columns D-W indicates they are scheduled to work during that time frame.

Originally Posted by Wyliecody

how familiar are you with VBA? regular conditional formatting requires more info that I can add but the work sheet becomes really confusing. I just don't want to give you something you wont be comfortable using and I also don't want you to have a really gummed up sheet if possible.

To be honest I'm not all that familiar with VBA. I took a C++ a few years ago, is there some relation there?
Anything you can do to get me started on this Wylie would be extremely helpful and greatly appreciated!

i dont know c++ so I am not sure the relation, let me try one more thing and I will show you the long way, then we can decide if VBA is the way to go. Oh and I assume you email this out the the agents or do you do a hard copy?

Originally Posted by Wyliecody

i dont know c++ so I am not sure the relation, let me try one more thing and I will show you the long way, then we can decide if VBA is the way to go. Oh and I assume you email this out the the agents or do you do a hard copy?

yep i email them. Thanks again Wylie!

Originally Posted by :) Sixthsense :)

Please explain the logic why the N12:S12 is highlighted with Blue Background and the T12 is Highlighted in Yellow?

Surely you will get solution even with CONDITIONAL FORMATTING or VBA

Hello,

The numbers at the top of the sheet indicate how many agents need to fill that "role" for the day. For example: N12:S:12 is highlighed blue because in those time frames there is a need of 5 agents handling complaints. The agents are first ranked in order (based off stats not listed on this sample) then this sheet follow a priority being:1st-Needed outside, 2nd- Needed In Office, 3rd-Needed Handling Complaints, Last or Lowest - Needed doing nothing.

Martin was selected for "outside" over William because he is higher ranked. William was selected for "office" in cell T12 because "office" is second priority and above "complaints" - Thus making him the candidate for office.

ok the long way, you have to create a new line of data with and if function similar to this =IF(AND(D$1>0,D6=$A6),$D$38,IF(AND(D$2>1,D6=$A6),$D$39,IF(AND(D$3>1,D6=$A6),$D$40,D41))) i did it using your time schedule as a reference. then you have to conditionally format the first cell to match with a formula like this =AND(D6="george",Y6="needed outside") then when you type in a name it will turn the color you want, you have to add a number to each formula like for cell d7 you would want d1 to be greater than 1 not zero. you would also have to create a new conditional format for each catagory i.e. needed outside, needed in office, etc., then because you email it you would want to lock the cells and hide them using just a format change to text color to white. this is very long form, I am working on an easy VBA code for you. I will see if I can get it debugged tomorrow night. let me know if you have questions.

Originally Posted by Wyliecody

ok the long way, you have to create a new line of data with and if function similar to this =IF(AND(D$1>0,D6=$A6),$D$38,IF(AND(D$2>1,D6=$A6),$D$39,IF(AND(D$3>1,D6=$A6),$D$40,D41))) i did it using your time schedule as a reference. then you have to conditionally format the first cell to match with a formula like this =AND(D6="george",Y6="needed outside") then when you type in a name it will turn the color you want, you have to add a number to each formula like for cell d7 you would want d1 to be greater than 1 not zero. you would also have to create a new conditional format for each catagory i.e. needed outside, needed in office, etc., then because you email it you would want to lock the cells and hide them using just a format change to text color to white. this is very long form, I am working on an easy VBA code for you. I will see if I can get it debugged tomorrow night. let me know if you have questions.

You sir, are the man. I will give this a shot in a bit (the long way). I will keep you posted. I am looking forward to the VBA code. Thanks again Wylie!! +rep

Originally Posted by smoke0509x

You sir, are the man. I will give this a shot in a bit (the long way). I will keep you posted. I am looking forward to the VBA code. Thanks again Wylie!! +rep

I gave this a shot. I'm not getting the desired result. I only getting 2 of the 4 applicable activities. Spreadsheet attached below. Thanks!
Sample.xlsx