Creating an HR management excel programm. Need advices for formulas :)

**Julien3000** · 04-19-2012, 06:00 AM

Hello everybody !

I have a list of 21 emmployees that will call slaves . However they still have rights. One of them would be to swap their days off.
One a week, the days off are from from Friday to Monday (some people have FRI-SAT, some SAT-SUN, some SUN-MON)
So 4 days possible, and for each of these four days, 5 different shifts are available :

Friday 7AM-3.30PM
Friday 8AM-4.30PM
Friday 9AM-5.30PM
Friday 10AM-6.30PM
Friday 12.00-8.30PM
Saturday 7AM-3.30PM
Saturday 8AM-4.30PM
Saturday 9AM-4.30PM
Saturday 10AM-18.30PM
Saturday 12.00-8.30PM
Sunday 7AM-3.30PM
Sunday 8AM-4.30PM
Sunday 9AM-5.30PM
Sunday 10AM-6.30PM
Sunday 12.00-8.30PM
Monday 7AM-3.30PM
Monday 8AM-4.30PM
Monday 9AM-5.30PM
Monday 10AM-6.30PM
Monday 12.00-8.30PM

Each employee will be asked what days and what shift he would agree to swap.

I have created a list of slaves, a dropdown menu with all the differents shifts.

Me for example i agree to work saturdays from 9-5.30 , 10-18h30 and 12h-20h30. so thats 3 possibilities out of 20 for me.

The aim of this excel table would be that when i use the dop down menu and click on Saturday 10-18h30 I would see my name glowing as i would agree to work this day

So basically when you would select one shift from the dropdown menu, you could see "glowing" all agents that agreed to swap this particular shift.

The 2 questions are, - how do I associate shifts to names ?

How do i make names highlighted if the shift corresponds ?

My excel sheet 1 is the list of the 21 names from A2 to A22 and a dropdown menu with all the 20 different shifts in B10

the 20 different shifts are sheet 2 from A1 to A20 (for th edropdown menu) .

I truely hope this is simple to do ... Please help me to help the slaves

THANKS A LOT in advance .

Ju

**antoka05** · 04-19-2012, 10:40 AM

See attached file, I hope it's what you need.

Regards,
Antonio

**Julien3000** · 04-24-2012, 07:55 AM

Hello Antonio,

this is just what i needed. Thats just perfect !! Thank you so much !

May I ask that you send me the links of the formulas you used so I can try and do it myself.

I need to understand how you did this so i can reshape it if needed.

A big thanks again

Regards

Julien

**Julien3000** · 04-24-2012, 08:47 AM

Hi Antonio,

I have been looking into it, so thats the exact idea, well done for understanding me. Now I have a question :

Is it possible to connect the names / Shifts / and association in order to :

When for the NAME1 and NAME7 i would tick for example G2 and L2 cells. Then i would go on the names sheet and in the drop down menu, i would choose the shift corresponding to G2 or L2 and i would see glowing in yellow the names 1 and 7 ?

Thanks a lot for your time

Regards

Julien

**antoka05** · 04-24-2012, 09:59 AM

Yes, you can work on sheet Names or on sheet Shifts. If you need to select the same time for more than one person you can go on Names sheet, select the time and click on all Names you want to select.

The same work can be done graphically more pleasant but it but it needs more time to program it.

Regards,
Antonio