Excel: Employee tracking (a whole new level)


Good day everyone! I previously posted asking for help on an employee attendance tracking template. Now I have another similar project on hand and would appreciate if someone can point me in the right direction. I have uploaded the template for reference. Link: https://www.dropbox.com/s/pyniotf5x8tju4s/Tracking%20Template.xlsx There are two cells for employees to either scan in (report for work) or scan out (knock off from work). This is done using a bar code scanner. Whenever an employee scans, his/her particulars will appear on the columns B7 and L7 using the VLOOKUP function. The lookup value is based on the entries registered under spread sheet ‘database’ which contains the information of all our employees. So whenever an employee checks in, an entry of his details should be reflected on row 25 under in office personnel. When this same employee knocks off from work, he will scan out and his entry will shift to out of office personnel instead. Do note that our employees work on shift work including weekends, therefore the check in and check out are usually days apart. Thus this above part will provide me a snapshot of the employees who are in office and out of office at any one time. Thus employees will appear either in office or out of office, depending on their scanning. On top of that, I am hoping to achieve another rather complex concept. That is, for example an employee checks in on the month of May 2014, his entry will appear on another spread sheet titled ‘May 2014’. Likewise, if he has check in in future on Dec 2014, his entry will appear on the spreadsheet of Dec 2014. Is this possible using macro? Subsequently, when he checks out, the date and time of checking out will be reflected on that very same entry that he checked in. So on one entry, I am able to see when he checks in and out. And in one spreadsheet, example May 2014, I can know when he checked in and out of office for May. This is a super complicated template… Hope to receive help from the experts here on this forum. Thank you so much!


This question generated 32 answers. To proceed to the answers, click here.

This thread is current as of May 29, 2014.


For more resources for Microsoft Excel