I have an interesting problem where I have --

Table 1 with 1 row containing revenue forecast values and 20 columns (1 for each week therefore 20 weeks) and

Table 2 with 6 rows & 12 columns in which the first row has 2 numbers either 4 or 5 which denotes the number of weeks that particular month has and the 2nd row contains months (Jan - Dec) and the remaining 4 rows are for 4 teams

We have pre-defined each month to have a set number of weeks.

I am looking to calculate the forecasts based on the condition if, for example, January month is a 4 week & February a 5 week month, then it checks the first row to see if its a 4 or 5 and then the sum formula will select the appropriate number of weeks from table 1. This should ideally also account for the fact that if a month is 5, then the subsequent month formula is also updated to not include that week 5 value in its sum.

Hopefully, this formula setup will also account for the fact that if we cut any cells from Jan as an example to account to team 2 starting in March, then the formula auto-updates.