Associating cells so that sorting them maintains relationship on another worksheet

[yankplayer87]

I am working on a payroll workbook in Excel 2010. I currently have it set up with 4 spreadsheets within the workbook. The first spreadsheet is a master list of all employees. Included in this spreadsheet is their name, pay rate, and pay source (whether they are paid through the department or through federal grants.

The second spreadsheet is the master payroll. I set up column 1 to automatically populate with the names from column 1 of spreadsheet 1. I used the following formula: =IF('Master Employee List'!A4="","",'Master Employee List'!A4). That formula continues down the column. Then, I manually type in the number of hours that they worked in the second column, and have a formula in the 3rd column which multiples those hours by the pay rate listed in the master spreadsheet. This continues for each pay period for the year.

The 3rd spreadsheet includes the same information from spreadsheet 2, but only includes department funded employees.

The 4th spreadsheet includes the same information from spreadsheet 2, but only includes federal funded employees.


The problem that I've encountered is: If I add a new employee into the master spreadsheet, once I sort that data, the information that I entered in spreadsheets 2, 3, and 4 are now all off by 1 row after that new employee's row. For example: If I add Abby Adams into the master spreadsheet and sort the data, when I go into spreadsheet #2, her name is listed at the top, but she has payroll information that used to be attributed to person that used to be first, but is now second on the list. Each employees information is now off by 1 row.

Is there a way so that the data that I'm manually entering in spreadsheet 2 always stays with that particular employee, even if another employee is added and sorted? Perhaps I just need to setup my spreadsheets differently so that I manually enter employee names on each spreadsheet.

[aydeegee]

Hi yankplayer87

I have used vlookup rather than your "=IF('Master Employee List'!A4".

Rather than having separate sheets for your pages 3 & 4, I have just used just the 1 payroll sheet, but then use a filter to see either Dept or FG (or both) (but you could still use separate sheets).

Note that I have used the subtotal function, so that only the visible cells are summed. The data can be sorted (and re-sorted) to your hearts content without changing the data.

One thing that will really screw things up for you is if someone gets a pay rise mid year. In this case you may have to either copy and paste values only for data at the old price. Otherwise you could issue a new Employee number for the new pay rate.

I hope this gives you some ideas.

Let me know if I can assist further

Regards
Alastair