Formula in a cell range

[Benny1]

I have a spreadsheet that calcs the XNPV in each column.

I want the range to decrease by 1 in each column.

i.e. in A1, I want the range to be A5:A20.
in B1, I want the range to be A6:A20
in B3 I want the range to be A7:A20

Is there an easy way to do this?

The spreadsheet is quite big and I do not want to manually do it.

Thanks for any help!

 

[Firefly2012]

Hi

You can use INDEX to resize as you go across columns eg:

=$A$20:INDEX($A$5:$A$20,COLUMNS($A$1:A$1))

copied across from eg cell A1 to C1 will progressively reflect A5:A20 then A6:A20 then A7:A20.

So this would cover the first two instances you highlight. Not so the range you want in B3, however