Changing a value without changing previous results

[Sharpshooter]

I'm the scorer for a pistol club and I inherited some spreadsheets which are basically tables that could have been created with Word. I've upgraded them to automatically perform many updates that were previously done manually, and I'm trying to decide the best way to attack this particular upgrade.

There are sheets with each shooter's grade, personal best and handicap. I'm creating another sheet to input weekly results. Points are awarded based on best overall score and best score on handicap each week. (A shooter's handicap is relative to their personal best.) I'm stuck between two objectives: if the spreadsheet detects a new PB for a shooter, I want it to update automatically, which isn't a problem, but this would in turn update their handicap which would then cause recalculation of previous weeks' handicap results.

The only way I can think to make this work is to have invisible cells with each shooter's scores on the grades sheet and then have some kind of calculation on the weekly sheet to determine which PB and handicap to use, but to be honest I'm not even sure I could make that work, and it does seem overly complicated. I was wondering if any of the gurus here have any ideas or could point me toward a function or method that would make it simpler. Hopefully I've explained well enough and hopefully the title is sufficient. An example wouldn't be much good but I'll answer any questions to clarify.

Extra info: Handicaps are directly related to PBs. The handicap applied to a weekly score is related to the best score before that day (i.e. if you shoot a new PB, your handicap for that shoot is still the old handicap.)