Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Employees list

Posted on 2016-10-12
3
Medium Priority
?
75 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 1000 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 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

618 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