Copying and updating references in Excel Workbook

[dekidroske]

I am working with a spreadsheet created by someone else and ran into a problem that needs a smart solution.

When I try copying the cell and pasting it in the cell to the right on the same row I get a bad result in two ways. The formula is:

=SUM('Specialty Post Call'!C437:C443)

The correct result should be =SUM('Specialty Post Call'!C444:C450). Instead I get D437:D443).

I fixed the Column error with the following:

=SUM('Specialty Post Call'!$C437:$C443)

But I cannot figure how to move the cell references 7 dates into the future.

[#2]

@dekidroske 

Let's say the first formula is in column B.

Change the formula to

=SUM(INDEX('Specialty Post Call'!$C:$C,7*(COLUMN(B2)-COLUMN($B2))+437):INDEX('Specialty Post Call'!$C:$C,7*(COLUMN(B2)-COLUMN($B2))+443))

Fill to the right.

[#3]

@dekidroske 

I cannot figure how to move the cell references 7 dates into the future.

That's not going to be a case of just copying and pasting a formula. I suspect you're going to want to incorporate the INDIRECT function. It would help me (or others here) help you if you could give us a more complete sense of the nature of the spreadsheet, how data are arrayed, what column you're using to put these formulas.

In the absence of that more complete picture of what you're facing, let me give you a link to a good resource for researching INDIRECT. Perhaps you can solve your own problem: 

https://exceljet.net/excel-functions/excel-indirect-function