Function that uses a defined number to determine range size

[rogerthatman]

Hey everyone, I really REALLY need your help.
I've searching and looking around the entire excel for a specific and a unique function. Unfortunately without success.
What actually I'm trying to do is creating a function that whenever I want it to make a MAX between some range, it will according to the number I entered.
I know it's really tough to understand like this. So I uploaded an example with the file (xlxs file) so you could understand. It's only 11KB and it will mean the world to me if you could download and help me:
http://www.2shared.com/file/ND-freie/Book2.html (you should click the "DOWNLOAD" blue button)
So whoever downloaded the file, here's an explanation:

There are lot of numbers in the "K" column. In "I7" there is a function which pick the MAX value according to the range I want. In the situation is the range between "K7:K17", which means - 10 numbers. And you can see I put the number 10 in bold, in above.
SO(!), what I actually want is to make the number 10 that I bold as a function - whichever value I put, it will range automatically in the "I7" function I did.
For example - If I put instead of number 10, number 20 - it will change the function in "I7" from this: =MAX(K7:K17), to this: =MAX(K7:K27). And now the range is up to 20.

I'm sorry for the mess and the long explanation and I will thank so much the one who actually rescued me from the frustration situation I face with.

Thanks a lot!!!

[NBVC]

Do you mean?

=MAX(K7:INDEX(K:K,7+A1))

where A1 is the cell you put the 10 or 20 in that defines the range.

[rogerthatman]

wow, thanks a lot. that's impressive. I feel so terrible now that you solved it in minutes and I've trying for hours haha
There's one thing that incomplete - It does really work with 10 or 20, but only that. I want it no matter number I put, it will define the range. The 10 and 20 numbers I mentioned were only for example.
May you help me further? It's really great so far.

[NBVC]

my function should add the number you put in "A1" to 7 to determine the range size.... it doesn't have to be 10 or 20....

I am not sure if I follow...

[rogerthatman]

You're right, I'm sorry. My confusion. I have one more last question and that's it
That's so great. But if I want to keep it further and make a lot more functions to this. I've created my last project with some changes and I would really like you to look one last time:
http://www.2shared.com/file/62-Loeqd/Book4.html


As you can see, the only thing that makes me stuck on this, is that after I put a function and then drag it down, the "K" value after the comma, doesn't change according to the first "K[number]" at the beginning of the function.
Do you know how to solve it? It will end it once and for all

Thanks a lot!

[NBVC]

You mean you want?

=MAX(K7:INDEX(K:K,ROWS(K$1:K7)+$A$1))

which "freezes" the reference to A1 and also adjust the "7" so the part after the comma corresponds to the part before the colon...

[rogerthatman]

That's about it
Amazing. Thanks for everything. I wish I would know at least half of what you know. Inspiring!

[NBVC]

This might make it a bit less confusing and perhaps more versatile...

=MAX(K7:INDEX(K:K,ROW(K7:K7)+$A$1))

I replaced ROWS(K$1:K7) with ROW(K7:K7)... so that anywhere you copy the formula the references are more manageable.