Getting rid of INDIRECT

[fred3]

I'm going to be adding columns in a fairly complex worksheet and want to get rid of column labels inside INDIRECT so that the formulas will remain relative references as columns are added.

I have this so far:

SUM(INDIRECT("R" & ROW()-X$4+1):INDIRECT(cell("ADDRESS",R7))

The ending point for the sum is already "fixed" and the column label "R" changes if columns are added ahead of column R.

But, the starting point for the sum is stuck on column R so the row number arithmetic can still happen.

How to change that part?

Thanks!

 

[shg]

=sum(r7:index(r:r, row() - x$4 + 1))