Hi Expert Excel user,
I need help to do indexing with counta function or any appropriate function combined to create intended result in my worksheet for a leave tracker. The table snippet should updated multiple lookup value from previous updated worksheet and criterion for value displayed should use:
Start Date
End Date
Employee Name
Annual Leave Total Emergency leave Total Medical Leave Total Child / Elder Care Total
1/13/2014 2 4/4/2014 1 1/22/2014 1
2/19/2014 2 3/31/2014 1
8/8/2014 1 9/29/2014 1 5/12/2014 1
9/12/2014 1 12/3/2014 0.5 6/17/2014 1
9/30/2014 5 6/18/2014 1
10/20/2014 5 10/29/2014 1
11/10/2014 1
The worksheet for main leave tracker are in formatted table accordingly and looks like this; and lookup value is uniquely defined by the color key.
Color Key PH Public Holiday AL Annual Leave MC Medical Leave ML Marriage Leave COM Compassionate Leave
TR Training CC Child Care/Elderly Care EL Emergency Leave MP Maternity/Paternity Leave EXM Exam
HOSP Hospitalised CC 0.5 CC Half day MC 0.5 MC Half day AL 0.5 AL Half day
January 2015
Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat
Employee Name 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Total Full Days CC Half day MC Half day ALHalf day
Afzal Mohsin 0 0 0
Zulfadzli 0 0 0
Ravimalar 0 0 0
Kogilavani MC MC AL AL 0.5 CC 4 0 0 0.5
Bavani 0 0 0
Sharrmila AL 1 0 0 0
Mary Shalini 0 0 0
Sony 0 0 0
Noorhafiza AL 1 0 0 0
Haziwan 0 0 0
Muzliawati 0 0 0
January Total 1 1 6 0 0 0.5
Ay help to point me in the right direction would be much appreciated
Aucun commentaire:
Enregistrer un commentaire