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.