table formulas not updating...always need to "copy-down" to get rid of #REF!s

BTW I have Calculations Options set to "Automatic" and the "Calculate Now" or "Calculate Sheet" do nothing. The only way to get rid of #REF!s and blank rows in the various tables is to copy the formula down again from the top of each table.

So I have a spreadsheet I use to manage a golf game at my home course and can't get the results tables to update each time I use it. Ideally I just want to open the spreadsheet enter the golfers and their scores for the day and then have the results and payouts auto calculated.

However I input data on the Data worksheet (see attached) like Date, add/delete players, change team IDs, I invariably end up spending 5-10 updating all the background tables used to calculate the results.

So if you open the attached spreadsheet you will see a small example of what I'm talking about. The A_table and B_table worksheets are used to count skins and low scores for the day (team+individual).

In the A_Table sheet cell AA6 shows a "#REF!". To fix it I need to stand in cell AA4 and then copy that formula all the way down....grrrr!

Moreover I will sometimes have blank rows out of the blue in the A & B tables, and I have no idea why. I never have to edit an formulas I just copy down and suddenly things work out fine.

Why does this happen?