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?

 

[pape]

You could use INDIRECT or OFFSET to define your range. For eaxmaple if
I wanted to find the largest value in column A for the the number of
rows as set out in G6, I would use something like:

=LARGE(INDIRECT("a"&ROW()-1&":a"&ROW()-(1+G6)),1)

The -1 is used to avoid circular references. Of course if you already
know the last row in the range then amend the formula