software

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:Kamp;Q9) 1
=MAX(K8:quot;Kquot;amp;Q9) 1
=MAX(K8:quot;Kquot;amp;INDIRECT(Q9)) 1
=MAX(K8:quot;Kquot;amp;LOOKUP(Q9)) 1

All with no success.

Any and all help appreciated.

Geoff.Hi!

Try this:

=MAX(INDIRECT(quot;Kquot;amp;K8amp;quot;:Kquot;amp;Q9)) 1

Which evaluates to:

=MAX(K5:K8) 1

Biff

gt; wrote in message oups.com...
gt; Ok - heres what we have:
gt;
gt; K1 = 7
gt; K8 = 8
gt; Q9 = 5
gt;
gt; Cell where function is to go is K9.
gt;
gt; What I am trying to do is look at the range from directly above cell
gt; (i.e. K8) to the cell in column K with the row the value of Q9 (i.e.
gt; K8:K5 in this case). The value in Q9 changes and I need to calculate
gt; the value of this cell as the Max value from this range plus 1. The
gt; value in this example should equal 9.
gt;
gt; I have tried several variations of the following:
gt;
gt; =MAX(K8:Kamp;Q9) 1
gt; =MAX(K8:quot;Kquot;amp;Q9) 1
gt; =MAX(K8:quot;Kquot;amp;INDIRECT(Q9)) 1
gt; =MAX(K8:quot;Kquot;amp;LOOKUP(Q9)) 1
gt;
gt; All with no success.
gt;
gt; Any and all help appreciated.
gt;
gt; Geoff.
gt;
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.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.This is what I would have thought was correct (cell is K9) but I just
keep on getting an quot;Invalid Cell Referencequot; error.

=MAX(INDIRECT(K8amp;quot;:Kquot;amp;Q9))

When I Evaluate the formula it equates to:

=MAX(INDIRECT(quot;;K5))

Seems strange that it contains a colon.Ok, now I'm confused!

Biff

gt; wrote in message ups.com...
gt; This is what I would have thought was correct (cell is K9) but I just
gt; keep on getting an quot;Invalid Cell Referencequot; error.
gt;
gt; =MAX(INDIRECT(K8amp;quot;:Kquot;amp;Q9))
gt;
gt; When I Evaluate the formula it equates to:
gt;
gt; =MAX(INDIRECT(quot;;K5))
gt;
gt; Seems strange that it contains a colon.
gt;
Got it!!!

=IF(MAX(INDIRECT(quot;Kquot;amp;(ROW()-1)amp;quot;:Kquot;amp;$Q9)) 1=0,quot;quot;,MAX(INDIRECT(quot;Kquot;amp;(ROW()-1)amp;quot;:Kquot;amp;$Q9))Biff, not Bill.

(I'm here to help lt;vbggt;.)
wrote:
gt;
gt; Thanks Bill, but I actually want the range to be from the cell directly
gt; above, to the row indicated by the value in the Q column.
gt;
gt; For example if Q21=5 and Q30=22, then
gt;
gt; K21 range needs to be from K20:K5 and
gt; K30 range needs to be from K29:K22.
gt;
gt; I can't seem to get this to work.
gt;
gt; Regards,
gt;
gt; Geoff.

--

Dave Peterson

Good deal!

Biff

gt; wrote in message oups.com...
gt; Got it!!!
gt;
gt; =IF(MAX(INDIRECT(quot;Kquot;amp;(ROW()-1)amp;quot;:Kquot;amp;$Q9)) 1=0,quot;quot;,MAX(INDIRECT(quot;Kquot;amp;(ROW()-1)amp;quot;:Kquot;amp;$Q9))
gt;