Value of cell to determine range in MAX Function
 

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.

Value of cell to determine range in MAX Function
 

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.

Value of cell to determine range in MAX Function
 

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.

Value of cell to determine range in MAX Function
 

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.

Value of cell to determine range in MAX Function
 

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.

Value of cell to determine range in MAX Function
 

Ok, now I'm confused!

Biff

wrote in message
ups.com...
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.

Value of cell to determine range in MAX Function
 

Got it!!!

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

Value of cell to determine range in MAX Function
 

Biff, not Bill.

(I'm here to help <vbg.)

wrote:

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.

--

Dave Peterson

Value of cell to determine range in MAX Function
 

Good deal!

Biff

wrote in message
oups.com...
Got it!!!

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