Solved

Employees list

Posted on 2016-10-12
3
50 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

863 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now