Bug - Sheet References

When I sort my table some of the formulas are referring to the wrong rows.
When you have formulas that use an unnecessary worksheet reference to the active worksheet, the sorting does not work.

Description

When data is entered onto a worksheet in a single block you can quickly sort it by any column.
This table has 4 columns.
The "Total" column has a formula that multiplies the "Quantity" by the "Price".
The grand total of these four items is then calculated by a formula in cell "E8".

You can use the Sort buttons on the Data tab to quickly sort this table by any of its columns.

You will notice that the grand total stays the same (£53.20) regardless of how you sort the table.
This is exactly what we would expect.

Formulas referring to Other Worksheets

You can include more complicated formulas in your tables and the sorting will continue to work as expected.
You can even include formulas that are referring to cells on different worksheets and the sorting will continue to work as expected.

Formulas referring to the Active Worksheet

When you have formulas that use an unnecessary worksheet reference to the active worksheet, the sorting does not work.
The "Total" column still has the same formula, the only difference is that the cells (D3, D4, D5 and D6) have been prefixed with the worksheet name.
When you are referring to cells on the same worksheet you do not have to prefix the cell references with the name of the worksheet.

Depending on how you construct the formulas and how complicated they are, you could end up with these types of cell references without even realising.
Notice that the formulas look the same and the grand total is also the same, still (£53.20).
Now sort this table by "Quantity" and you will see that the grand total has changed.

When it moved the "Hats" row down, it did not change the cell reference from "D4" to "D6".
The formula in cell "E6" is now incorrect and is returning a different value.
When it moved the "Boots" row up, it did not change the cell reference from "D6" to "D4".
The formula in cell "E4" is now incorrect and is returning a different value.

Conclusion

If you are sorting your data and seeing that the actual formulas are returning different values, double check the original formulas.
Remove any unnecessary worksheet references to the active worksheet.