Value of cell to determine range in MAX Function

[geoff_durham]

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.

 

[Biff]

Hi!

Try this:

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

Which evaluates to:

=MAX(K5:K8)+1

Biff

 

[geoff_durham]

Thanks Bill, but I actually want the range to be from the cell directly
above, to the row indicated by the value in the Q column.

For example if Q21=5 and Q30=22, then

K21 range needs to be from K20:K5 and
K30 range needs to be from K29:K22.

I can't seem to get this to work.

Regards,

Geoff.

 

[geoff_durham]

Just to clarify - I want to copy this cell to all cells in this column
so the formula will always recalculate to detemine the range to be from
the cell directly above to the row indicated by the value in the Q
column.

Geoff.

 

[geoff_durham]

This is what I would have thought was correct (cell is K9) but I just
keep on getting an "Invalid Cell Reference" error.

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

When I Evaluate the formula it equates to:

=MAX(INDIRECT(";K5))

Seems strange that it contains a colon.

 

[Biff]

Ok, now I'm confused!

Biff

 

[geoff_durham]

Got it!!!

=IF(MAX(INDIRECT("K"&(ROW()-1)&":K"&$Q9))+1=0,"",MAX(INDIRECT("K"&(ROW()-1)&":K"&$Q9))

 

[Dave Peterson]

Biff, not Bill.

(I'm here to help <vbg>.)

Thanks Bill, but I actually want the range to be from the cell directly
above, to the row indicated by the value in the Q column.

For example if Q21=5 and Q30=22, then

K21 range needs to be from K20:K5 and
K30 range needs to be from K29:K22.

I can't seem to get this to work.

Regards,

Geoff.

 

[Biff]

Good deal!

Biff