Solved

Employees list

Posted on 2016-10-12
3
69 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
need copy macro to select range to copy 23 45
VBA routine modification/tweak needed 10 50
sort time order 10 39
LOOK FOR 22 23
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

740 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