Excel formula for a 2010 template - Employee Attendance Tracker

Hi,

I need help editing a 2010 template.

I started editing a pre-loaded excel template in 2010 version named "Employee Attendance/Leave Tracker." You can access this template directly from excel or it can be found here: http://www.bluelayouts.org/template/1785.html

On the top right hand corner, there's a drop box that allows a user to select an employee. Once an employee is selected, the calendar is automatically changed based on the pre-recorded data.

What I need help is to create another option inside the "Select an Employee" drop box, where it will combine ALL employees, then automatically change the calendar as well as the boxes on the bottom of the pages. The boxes are populated using formulas.

Employee-Tracking-template-8865.jpg


Please help. Thank you in advance.

Sorry - my AV is blocking access to that site. Please attach the workbook here.

Looking at the image, however, it might not be as easy as you think: for example, the colour coding is fine for one employee, but what is going to happen when you have several 'events' happening on the same day? I think you are probably going to need to create a separate dashboard for the information about all employees, because the data is going to need to be processed altogether differently.

EDIT: Yes, the template is available in the latest version of Excel.

Attached is a version of the file with an extra tab added. I've adapted the formulae for the key statistics and removed the calendar (as the colour-coding doesn't work for more than one employee at once). I have not thoroughly checked it, but it seems to be working. Does this give you enough information? The tab shows information for all employees.

Did you look at my attachment?

Yes, I did. Though, my need is a little bit complex. Please see attached.
1.PNG
2.PNG


I am trying to create almost the same as the calendar. But this time, it is actually calculating how many employees. I need a formula for the following tasks:

(1) to connect the drop-down "select a month" to row 9. So it automatically populate the month and pull the information from the "Employee Leave Tracker" tab. So, if user select February, then rows 9 & 10 data will be automatically updated. Note that this should also be connected with the year.

(2) The information that I need to populate in row 10 are the count of employees working for the day. For instance, on January 1, there were 3 employees working, etc...

>>>Update: I was able to populate this automatically. However, my formula is not connected to the "Select a Month" drop-down list. When I select February on the drop down, the values remain the same :-( I may have been missing the valSelEmployee but not sure how to incorporate it to this tab since it is a little different than the 'calendar view' tab.

>>> The cell formula I used is =SUMIFS(tblLeave[Days],tblLeave[Start Date],"="&DATE($AI$5,1,I9),tblLeave[Time-In],">"(7,9,0))

(3) Lastly, is to create a line graph as a report. "Time-In" will be the y-axis and "Start Date" will be the x-axis. The graph will display measuring employees. So the points on the graph will have the labels Employee 1, Employee 2, Employee 3, etc...

I am playing around it, trying to figure it out. I hope you can help me.

Thanks!