Hi!

Try this:

=MAX(INDIRECT("K"&K8&":K"&Q9))+1

Which evaluates to:

=MAX(K5:K8)+1

Biff

wrote in message
oups.com...
Ok - heres what we have:

K1 = 7
K8 = 8
Q9 = 5

Cell where function is to go is K9.

What I am trying to do is look at the range from directly above cell
(i.e. K8) to the cell in column K with the row the value of Q9 (i.e.
K8:K5 in this case). The value in Q9 changes and I need to calculate
the value of this cell as the Max value from this range plus 1. The
value in this example should equal 9.

I have tried several variations of the following:

=MAX(K8:K&Q9)+1
=MAX(K8:"K"&Q9)+1
=MAX(K8:"K"&INDIRECT(Q9))+1
=MAX(K8:"K"&LOOKUP(Q9))+1

All with no success.

Any and all help appreciated.

Geoff.