Dynamic Cell Range

[nickcarda]

I've been trying to figure out how to get dynamic range selections.

I have a list of numbers in one column and in the adjecent column I
want it to give me the largest number in the previous n rows, where n
is a number in another cell. anyone know how this is or can be done?

 

[Dave Peterson]

As a worksheet formula?

I put n in A1.

I put my numeric data in D130
In E30, I put this formula:
=MAX(OFFSET(D30,-A1,0,A1,1))
It evaluated the same way as:
=max(d20:d29)

If I wanted the equivalent of:
=max(d21:d30)
Then I'd use a formula like:
=MAX(OFFSET(D30,-A1+1,0,A1,1))

 

[Nick]

Thanks, I got what I want
Cell C5=MAX(B5:OFFSET(Sheet1!B5,0,0,Sheet1!$E$1,1))

Where my colum of data starts in B5 and n is in cell E1. When I
drag/copy into the rest of the cells in column C it updates the B5 to
B6, B7, etc.