Find the top value in a list

  • Thread starter Thread starter JGB
  • Start date Start date
J

JGB

I have a worksheet created in EXCEL 2003 which i use to record swimmers times
in several lists. One examlpe is in cells D109 to 113. In cells D111 to 113
are recent times and D109 to 110 are empty. As I add current times to the
top of the list. I want to be able to retrive the current value at the top
of the list and show it in D106. D107 & 108 are used for other values. At
present I have the following formula in cell D106
=OFFSET(D108,COUNTBLANK(D109:D113)+COUNTA(D109,D113),0). This works well but
makes updating the list difficult. Is there a simpler way to achive the same
result?

Thank you in advance
 
I have a worksheet created in EXCEL 2003 which i use to record swimmers times
in several lists.  One examlpe is in cells D109 to 113.  In cells D111 to 113
are recent times and D109 to 110 are empty.  As I add current times to the
top of the list.  I want to be able to retrive the current value at thetop
of the list and show it in D106.  D107 & 108 are used for other values.  At
present I have the following formula in cell D106
=OFFSET(D108,COUNTBLANK(D109:D113)+COUNTA(D109,D113),0).  This works well but
makes updating the list difficult.  Is there a simpler way to achive the same
result?

Thank you in advance
Click to expand...

One possible solution is to create a dynamic range with a name and
then have a formula w/ =min(named_range) in cell d106
 
Think you could try this in D106, normal ENTER:
=INDEX(D109:D113,MATCH(TRUE,INDEX(D109:D113<>"",),0))
which will always retrieve the "topmost" value within the range D109:D113.
Adapt the range to suit.

Success? Celebrate it, click YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
 
Thank you Max it works a treat

Max said:
Think you could try this in D106, normal ENTER:
=INDEX(D109:D113,MATCH(TRUE,INDEX(D109:D113<>"",),0))
which will always retrieve the "topmost" value within the range D109:D113.
Adapt the range to suit.

Success? Celebrate it, click YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
Click to expand...
 
Back
Top