Your cart is currently empty!
Excel – Employee Tracker Spreadsheet Part-1
Excel: “VBA” Overhaul of plain “Employee” Spreadsheet with added functionality.
Employee Tracker Microsoft Excel Spreadsheet Enhancement
Problem Statement: Customer of a Small Manufacturer that supplies Educational and Promotional materials to a major metropolitan Public School System.
Problem Statement:
We need a Fix for the problem of difficult to maintain Spreadsheet by Adding Automation and Data Entry Forms.
Company: Frost-Design LLC’s Problem Statement
Frost-Design employs between 50 to 150 Employees on a seasonal basis. They take Product Orders year-round but only manufacture (6) Months out of the year, and the bulk of the Product Deliveries are from from April through late June.
Frost-Design use a plain Microsoft Excel Spreadsheet to track the following…, Employee Data, Salary, Hours, Sick-Time, Vacation and more.
ITHDS was tasked with how to make the current Employee Spreadsheet easier to use and off-load most of the maintenance and updating of the Spreadsheet to automation and select End-Users.
Currently the Spreadsheet has 20 plus columns that lack validation when updating, and is error prone due to the linear Scrolling back and forth when entering Data.
The Owner, his Wife a couple of trusted Employees have to update the Spreadsheet by going to each Employee an or copying information from another Excel Time-Sheet Spreadsheet on a daily and weekly basis.
Also the use use of the term Employee is misleading since the Company intermingles actual Employees, with Consultants, and Contractors as entries in the Spreadsheet.
The Owner would like to track Labor Cost ant Time and Time Off, and Vacation per Employee and Department
Frost-Design’s Current Spreadsheet
Please be advised that the data within this Blog has been scrub and does not represent real people
This solution is for Educational purposes and all its contents are fictional. Any resemblance to real life figures or characters is purely coincidental!
Solution ITHDS provided: Is a dynamic Dashboard to get a High-Level View of Employees by Department, Base-Pay, across Shifts
Spreadsheet for Employees to Track Time, PTO, and Sick-Time.
⦁ Create individual Time sheet for Employees in Shared Folder with Authentication
A little Video Snippet of the new UserForm in action.!
Step #1 We created Data Entry Form “Add New Employee”
To reduce Spreadsheet Scrolling and Data Entry Errors by adding Validation designed to enhance your tech experience.
Step #2 Created Data Entry Forms “Job Titles”, “Status” , & “Departments” a Edge Tech Solutions
Each Form has an associated List on a “Hidden”Tab that is maintained and updated dynamically whereby as items are added and or removed the list will sort and populate automatically.
Step #3 Created a “Search” Form that allows for searching of Employees by “Name(s), Department, and Title”
The Search Form not only allows for searching by Name, Department, and or Title, it eliminates the need to access the Worksheet directly or even have to Edit the original Spreadsheet by scrolling back and fort with an intuitive interface.
Employee -Tracker
Solution
Customer’s original Table where Columns with “orange letters” represent the Added Columns
** Be advised that the Data is simulated /fake to facilitate sheet functionality and testing.
Our First Draft for Client Testing.
Main Screen with Charts showing Employess by Department, Base-Pay Distribution by Department, and Employees by Shift…!
Created VBA ‘UserForms’, to allowed for consistent control over Job Titles, Departments,
Frost-Designs LLC’s Problem Solution
Step#1 Create Data Entry Form(s)
to reduce Scrolling and Data Entry Errors by adding Validation
New Employee Form: Created dedicated VBA UserForm to add new Employees
Job Tiltle List and Data Entry Form: Created dedicated VBA UserForm to add new Employees
New Employee Form: Created dedicated VBA UserForm to add new Employees
New Employee Form: Created dedicated VBA UserForm to add new Employees
A Dashboard to get a High-Level View of Employees by Department, Base-Pay, across Shifts
Make the current Employee Spreadsheet easier to use and off-load most of the maintenance and updating of the Spreadsheet to to End-Users. Currently the Spreadsheet has 20 plus columns that lack validation when updating, and is error prone due to the linear Scrolling back and forth when entering Data.
Currently the Owner and or his Wife have to update the Spreadsheet by going to each Employee an or copy information from another Excel Time-Sheet Spreadsheet on a weekly basis.
Also the use use of the term Employee is misleading since the Company intercomigles actual Employees, Consultants, and Contractors as entries in the Spreadsheet.
The Owner would like to track Labor Cost ant Time and Time Off, and Vacation per Employee and Department
Solution Design Offered by ITHDS
Add Custom Data Entry Forms with validation Fields to reduce errors.
⦁ Add Additional Fields to Help with Data Collection
⦁ Create Menu driven Interface system to control Access to Spreadsheet.
⦁ Add dynamic List to assist with Data Entry
⦁ Add Rules Based Access to Spreadsheet.
⦁ Add Charts for High-Level Views of Employees by Department, Employees By Shift,
and Employees by Status/Type.
⦁ Create Satellite Spreadsheets with Data connection back to the Employee-Tracker Spreadsheet for Employees to Track Time, PTO, and Sick-Time.
⦁ Create individual Time-sheet(s) for Employees in Shared Folder with Authentication.
Need Help Like this…., ?
Absolutely, it’s possible. Reach out to our tech support team to Open a Ticket for assistance. The Cost is around $99 per Upgraded Spreadsheet of 40 Columns or less, and commensurate at a Rate of 10 Columns at $50 per additional 10 Columns. Additional Fees apply for Formula Complexity beyond basic Math of Sum, Avg, Percent, Etc.
Looking to get your Spreadsheet Upgraded
Do You want Help with your Spreadsheets further?
If So…, Go to our HelpDesk Page and Open a Ticket and Select Excel for the Ticket Type and
fill-out the Form with Details explaining your Spreadsheet and then attach a Copy of your current Spreadsheet
that you require Help.
Please be sure “Not” to include Personal info like SSN#, Drivers Licenses, Etc.