AutoFill Error

Hi everyone, new user here but I have read over this site in the past and appreciate everyone's input.


I am having a problem getting excel to recognize the pattern I am attempting to autofill.

On a new sheet, I am pulling the sum of 7 rows and 52 columns from the previous sheet. Each of these sums amounts to one week of info; however, I want to be able to drag down so that I can have the sum of the next 7 rows and 52 columns for the next week on the second sheet. This is the formula I have created in D17: =SUM(Sheet1!$C6:$BB12). Underneath it on the next 2 rows (D18 and D19) on sheet 2, I have entered the following formulas, respectively: =SUM(Sheet1!$C13:$BB19), =SUM(Sheet1!$C20:$BB26). The problem is, when I autofill by dragging the highlighted formulas into the next rows (D20 and D21), it autofills in these formulas: =SUM(Sheet1!$C9:$BB15) and =SUM(Sheet1!$C16:$BB22) respectively. Instead, I would like for it to return: =SUM(Sheet1!$C27:$BB33) for D20 and =SUM(Sheet1!$C34:$BB40) for D21, etc.

Sorry about being long-windedness, I just have not been able to figure this one out. Any help would be greatly appreciated. Thanks in advance.

Can anyone tell me how to fix this issue?