Solved

Employees list

Posted on 2016-10-12
3
63 Views
Last Modified: 2016-10-17
I Have an employee list that has over 300 employees. This changes every month as people leave and new one are hired. I need to keep track by entering the new employee in the month that they were hired. I also need to keep a master list. Is there a way to just enter the new employee in to the month that they were hired but have a macro or Visual Basic code to transfer the new data every month to the master list but not delete the list that is there already. It would need to check for any employees that have left as well and update the master list with that. The master list would be the first sheet. Using office 2010. I have attached a sample of what I need.
Thank you.
Employees.xlsx
0
Comment
Question by:jodyreid
3 Comments
 
LVL 1

Assisted Solution

by:Robert Brown
Robert Brown earned 250 total points
ID: 41840943
Just off the top of my head. I will keep a Master List of all employees with a status field. Active, Terminated, Suspended, Layoff etc. Then have a query report pull based on the status field. An update query could pull the various criteria to another table,if you needed them in a separate historic archive databases. I hope this helped.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 250 total points
ID: 41841167
I'm in agreement with metacomputer in the sense that you should keep one central Master List that you will update and edit.  Then, you could have derivative monthly sheets that "pull" the appropriate data (in your case, all employee information for those hired in that month) to their respective sheets.  This is a fairly standard way to set up databases of information and you'll be better-served to use this construction.

Now, your example workbook shows January hire dates in the [Mar] sheet, so I've taken the liberty of changing them to March dates.  I also think your Master List should include the termination date, which could also serve as a "end of status" date (for example, an employee changes roles or locations).  But for the simple purposes here, we'll leave it out.

In my example workbook, all employee information is stored in the [New Employees] sheet.  I've added a new column called "HireMonth" that displays the abbreviation for the month of the Date of Hire.

Then, each month sheet contains a PivotTable that shows just the employees whose month of hire equals the same month (ex., "Jan", "Feb", "Mar").  You do have to refresh those sheets whenever you update the data on the [New Employees] sheet, but you can refresh all PivotTables at once via the menu (PivotTable Tools, Options, Refresh, Refresh All).

-Glenn
EE-Employees.xlsx
0
 

Author Closing Comment

by:jodyreid
ID: 41846866
Thank you
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question