Excel 2007 : Formula Cell References Change When Sorting After Filtering

I have encountered an odd problem in Excel 2007. I am referencing cells in formulas. When I filter and then sort the records, the cell references change. Does anyone know why?

Below is some example data, which I have placed in an Excel's columns A-F and rows 1-10. Also, is an attached PDF with the example data.

Here is what I do to get the error. First I go to "Data > Sort & Filter > Filter" to add the drop down menus in the data column headers. Then I filter by "Grp", so the records for only one "Grp" is displayed. Finally, I sort the group data by "Yrs Emp". This will cause the cells referenced in "Emp Pct of Grp Bns" to change, which makes the results wrong.

Formula for "Emp Pct of Grp Bns": =D2/(D2+D4+D6+D8+D10)
This formula is for "Grp" 1 records. The cell being devided, in this case D2, will be changed to D4, D6, D8, and D10 for their respective rows. "Grp" 2 records has a simular formula that only references the "Grp" 2 data.

Formula for "Emp Bns": =B2*E2
The row number will change to the row the formula is located.


Grp Grp Bns Emp ID Yrs Emp Emp Pct of Grp Bns Emp Bns
1 5000 A 1 =D2/(D2+D4+D6+D8+D10) =B2*E2
2 4000 B 5 =D3/(D3+D5+D7+D9) =B3*E3
1 5000 C 10 =D4/(D2+D4+D6+D8+D10) =B4*E4
2 4000 D 1 =D5/(D3+D5+D7+D9) =B5*E5
1 5000 E 5 =D6/(D2+D4+D6+D8+D10) =B6*E6
2 4000 F 10 =D7/(D3+D5+D7+D9) =B7*E7
1 5000 G 1 =D8/(D2+D4+D6+D8+D10) =B8*E8
2 4000 H 5 =D9/(D3+D5+D7+D9) =B9*E9
1 5000 I 10 =D10/(D2+D4+D6+D8+D10) =B10*E10

Nice pdf, wouldn't the excel file be more useful?

Here is the Excel version.