Reference a moving cell in another table

I have a worksheet that contains two tables, one above the other. The first table is constantly being resorted (through the use of a macro). The second table is intended to be a summary of the first and each cell contains a simple formula that takes two specific cells in the first and averages them.

The problem is that when the first table is resorted, the references don't change in the second table, and they then point to cells in the wrong row. Using absolute references doesn't help.

Is there a solution to this? I don't think moving the second table to another worksheet would solve anything, but maybe that's wrong.

Please upload an example of what you have and someone (maybe even me) will provide a solution.

Try this. The intention of the second table would to add the totals from two rows in the first table representing sales teams. To sort the first, uses Ctrl+Shift+S.