I've run out of google foo on this one, and I'm finally hoping those smarter than me can assist/guide me in what I'm trying to accomplish.
Some background; I'm trying to make a file that tracks Employees of a company with certain criteria, and I'm not only trying to make it easier for me each month when it's updated, but also make it easily usable for other people in my company who might not be as excel savvy.
An employee roster is run each month through HR.
Given these two factors, I've decided the simplest way to handle getting this information into my file is just deleting last month's data and copying and pasting current data into the 7 columns and their respective headers, while locking the headers I need in row 1, and then locking the corresponding formulas on this page. The HR roster file is then "discarded".
Once this information is successfully copied and pasted into the appropriate columns, I run a few basic formulas to extract some information for use in a custom table and a Pivot Table
Now, here is where I get to the question: My aforementioned Pivot Table depends upon a column called "Employee Skill Level" and this has to be a user inputted value. It cannot be stored in the HR report. The issue is, if an employee has been added/removed from one month to the next then simply copying and pasting the needed information into the fields like I described above will break the associated "Employee Skill Level" to the employee.
My solution that I started working on is to have a new sheet that is simply called 'Employee Skill Level'! and this would be where the user manually inputs the skill level (C:C in my example, up to 495 employees) of the Employee.
I have it built like this:
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]Employee #[/TD]
[TD]Employee Name[/TD]
[TD]Employee Skill Level[/TD]
[/TR]
[TR]
[TD]=IF('Current Roster'!A2<>"",'Current Roster'!A2,"")[/TD]
[TD]=IF($A2<>"",INDEX('Current Roster'!$B2:$B$495,MATCH($A2,'Current Roster'!$A2:$A$495,0)),"")[/TD]
[TD]USER INPUT[/TD]
[/TR]
[TR]
[TD]=IF('Current Roster'!A3<>"",'Current Roster'!A3,"")[/TD]
[TD]=IF($A3<>"",INDEX('Current Roster'!$B3:$B$495,MATCH($A3,'Current Roster'!$A3:$A$495,0)),"")[/TD]
[TD]USER INPUT[/TD]
[/TR]
</tbody>[/TABLE]
With the above formulas, the new sheet will auto populate with the 'Current Roster'! information. I admit it seems a bit convoluted, but 'Current Roster'! uses this:
Which just uses the Employee # from 'Current Roster'! to reference back to 'Employee Skill Level'! and pull the text stating that employee's skill.
My issue becomes this is circular. I think my solution needs to be somehow keeping one sheet as the stable calculating piece, and I think the 'Employee Skill Level'! is the one - it doesn't really matter if I have terminated employees on here since 'Current Roster'! won't pull their info.
SO ... the question:
Some background; I'm trying to make a file that tracks Employees of a company with certain criteria, and I'm not only trying to make it easier for me each month when it's updated, but also make it easily usable for other people in my company who might not be as excel savvy.
An employee roster is run each month through HR.
- Each time it's run by HR, the column layout is dependent on checking/un-checking boxes in the reporting program, so it's almost always guaranteed to be slightly different than the last month's run
- The excel file contains a sheet of 'Current Employees'!, and a sheet of 'Terminated Employees'!, with seven headers.
- Employee #
- Employee Name
- Job Type
- Job Step
- Title
- Date Pay Started
- Hourly Rate
Given these two factors, I've decided the simplest way to handle getting this information into my file is just deleting last month's data and copying and pasting current data into the 7 columns and their respective headers, while locking the headers I need in row 1, and then locking the corresponding formulas on this page. The HR roster file is then "discarded".
Once this information is successfully copied and pasted into the appropriate columns, I run a few basic formulas to extract some information for use in a custom table and a Pivot Table
- Month Hired
- Year Hired
- Union or Non-Union
- Employee Skill Level
Now, here is where I get to the question: My aforementioned Pivot Table depends upon a column called "Employee Skill Level" and this has to be a user inputted value. It cannot be stored in the HR report. The issue is, if an employee has been added/removed from one month to the next then simply copying and pasting the needed information into the fields like I described above will break the associated "Employee Skill Level" to the employee.
My solution that I started working on is to have a new sheet that is simply called 'Employee Skill Level'! and this would be where the user manually inputs the skill level (C:C in my example, up to 495 employees) of the Employee.
I have it built like this:
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]Employee #[/TD]
[TD]Employee Name[/TD]
[TD]Employee Skill Level[/TD]
[/TR]
[TR]
[TD]=IF('Current Roster'!A2<>"",'Current Roster'!A2,"")[/TD]
[TD]=IF($A2<>"",INDEX('Current Roster'!$B2:$B$495,MATCH($A2,'Current Roster'!$A2:$A$495,0)),"")[/TD]
[TD]USER INPUT[/TD]
[/TR]
[TR]
[TD]=IF('Current Roster'!A3<>"",'Current Roster'!A3,"")[/TD]
[TD]=IF($A3<>"",INDEX('Current Roster'!$B3:$B$495,MATCH($A3,'Current Roster'!$A3:$A$495,0)),"")[/TD]
[TD]USER INPUT[/TD]
[/TR]
</tbody>[/TABLE]
With the above formulas, the new sheet will auto populate with the 'Current Roster'! information. I admit it seems a bit convoluted, but 'Current Roster'! uses this:
Which just uses the Employee # from 'Current Roster'! to reference back to 'Employee Skill Level'! and pull the text stating that employee's skill.
My issue becomes this is circular. I think my solution needs to be somehow keeping one sheet as the stable calculating piece, and I think the 'Employee Skill Level'! is the one - it doesn't really matter if I have terminated employees on here since 'Current Roster'! won't pull their info.
SO ... the question:
