Track weekly overtime worked and available ytd

We are having problems with a spreadsheet where OT hours worked each week are input and totaled ytd. Additionally, the overtime available each week is automatically calculated from this initial input with formulas and totaled in a ytd column.

When we sort based on the available OT ytd (the last column), the formulas are no longer referencing the correct cells. We tried using the $ options, but that did not seem to help or are using them incorrectly.

Attached is our draft spreadsheet. Any ideas or assistance is appreciated.

It may not be possible. I don't know.

The formulas in both columns (E and F) are cumulative where the weekly OT hours are added to the previous OT YTD and to the available OT YTD. The sort is performed on columns D,E and F with F being the key.

The formulas after the sort no longer have the correct relationship. The available OT YTD cells sum the row and all rows beneath it. After the sort, the formula stays the same and does not perform the proper totaling of cells beneath that row.

I can't make sense of your attached file...
It certainly does not seem to match your description of your problem!

moving references on sorting is often a problem,
but without being sure what you are trying to do, it is difficult to suggest a solution.