Thanks for all the help, Vinodsralian's solution ended up working, for some reason auto calculate and manual calculate don't work with this formula, after I have pasted the new weeks values I have to highlight the relevant section in the aggregate sheet and click on calculate in the lower left, then the rest auto calculate for the week.

I really appreciate both of you taking the time to help on this one, it has brought up a new challenge, I will try to tackle by myself, but I may come back and make a new post if I can't figure it out!