Find the top value in a list

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
 
J

JoelS

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
 
M

Max

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
 
J

JGB

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...
 
J

JGB

Thanks Joel I will try it out

JoelS said:
One possible solution is to create a dynamic range with a name and
then have a formula w/ =min(named_range) in cell d106
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

QP formulas not working in Excel 2
Sorting Text Fields Containing Letters and Numbers 1
Find and Replace Cell Values 3
Cell value in a formula --> Indirect function? Help please... 3
Hide Rows in one W/S Based on a cell value in Another W/S 7
Need to flag the lead ID of any contiguous ID's from a sorted list of ID's. 5
creating a top 10 list 2
Multi-select list box 1

Top