Automatically populate table based on week number

Hi,

I'm currently looking for a Index/match type formula that can populate a table depending on which week it is.
I have two sets of tables.
Table A contains scores from a weekly questionnaire my teams take with the last column being a calculated average.
I want Table B to automatically get the average scores from Table A while still keeping previous weeks average scores (See attached example).
At the end of the year I will be able to see how performed throughout the year.

Is this something that's possible to do with formulas or do I need to start learning VBA?

A formula can contain a value or a cell not both.
And you want a formula in Week 8 K7:O7 based on the averages in H4:H8
But when those averages in table A are updated in a two weeks time, the summary in table B for week 8 will automatically change since the formulas will still point to Table A H4:H8.
This is not what you want, you want them to remain the same or change to values since there's no history on Table A.
Therefore it's not possible to do this via formulas.
VBA, yes (though I'm no expert at VBA).

Or make table A have an expanding table so it has a history of previous scores and make table B a summary with averages.
Though your next problem would be for Table B rows to calculate averages for a specific week / fortnight though that shouldn't be too complicated.