Building an Attendance Tracker

[Jeremy]

I need Help! The only way I can think to do this is create a separate
worksheet for each employee which we will interact with on a daily basis.
I'm struggling because I want to have a worksheet that collects all of the
data from each employees worksheet and puts it all together in one view. I
would love some suggestions on how to do it or on how I should design this
Attendance Tracker. FYI there are about 550 employees.

 

[Pete_UK]

Tell us what data you need for each employee - will it be start time, finish
time, hours worked etc, or just present, leave, sick etc?

I would suggest having a sheet for each week, where your 550 employees are
listed on a new row for each, and columns are used for different days.

Hope this helps.

Pete

 

[JLatham]

Like Pete_UK said, we need to know how you're tracking Attendance and how you
want to 'collate' the data.

I built a system for a company to do this that may be more elaborate than
what you are looking for, but I'll describe it just in case it gives you some
ideas.

General files structure:
There is an AttendanceRecords folder
Under AttendanceRecords there are folders for each DIVISION in the company
Within each DIVISION folder there is are individual attendance files for
each employee. There is also a SUMMARY folder in this folder.

There is one sheet in each employee .xls file that has the 12 months of the
year in rows, with day of the month in columns. A "reason for absence" is
entered into a cell for any workday that the employee is not at work.
Reasons could be things like V for vacation, U for Unexcused, S for sick or
personal day off, T for tardy, L for left shift early and some others. No
reason that all of these sheets couldn't be in a single workbook - this
company just wanted separate files for each employee.

OK that single workbook/sheet gives a detailed view of an employees
attendance for any month of the year.

In the SUMMARY folder for that division, there is a file that uses some
macro code to examine the individual employee folders for that division and
give a summary by month, calendar quarter and year to date of all absent
days that are "unmitigated" - that is they didn't have a good reason such as
vacation or sick. This file gives management a quick look at the overall
picture for the division.

Way up at the top level, in the AttendanceRecords folder, there's a
CorporateSummary workbook that also uses macro code to get the contents of
the various division summary workbooks and provides the employee name and the
division they are in along with the monthly, quarterly and year to date
absence counts for each individual in the corporation.

Again, there's no reason, except for the size of the workbook, not to have
all of this information available in a single workbook: 550 individual
employee sheets, 1 additional sheet per company division, and 1 sheet to
provide the total corporate overview of attendance.

 

[Jeremy]

JLatham That is exactly what I am looking to build. The macro is what I need
help with. I want the individual working with this tracker to be able to
click directly to that employees personal page, enter the data on that page
and have it update the "master sheet." It will be a large file but I would
prefer to keep it all on one. I am tracking the same basic data as you. On
each employees page it will be a little more simple than the one you built;
instead of a calendar it will simply be 3 drop downs for absences and 3 drop
downs for late.

 

[JLatham]

Perhaps if you can send me a sample .xls file with a few sheets in it set up
generally like you plan things to be and some explanation of how things
should 'roll up' and what needs to go into those drop downs (and what to do
with entries that are selected), stuff like that, perhaps I can lend a hand.
You can reach me at (remove spaces):
Help From @ jlatham site.com

 

[Jeremy]

Jlatham I've sent you a sample of what I'm looking for. Let me know what you
think please.

 

[JLatham]

Give me some time to look it over, I did receive the email and file.