Find the top value in a list

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

0
jgb (10)
5/21/2009 3:04:20 PM
excel 39879 articles. 2 followers. Follow

5 Replies
568 Views

Similar Articles

[PageSpeed] 47

On May 21, 11:04=A0am, JGB <J...@discussions.microsoft.com> wrote:
> I have a worksheet created in EXCEL 2003 which i use to record swimmers t=
imes
> in several lists. =A0One examlpe is in cells D109 to 113. =A0In cells D11=
1 to 113
> are recent times and D109 to 110 are empty. =A0As I add current times to =
the
> top of the list. =A0I want to be able to retrive the current value at the=
 top
> of the list and show it in D106. =A0D107 & 108 are used for other values.=
 =A0 At
> present I have the following formula in cell D106
> =3DOFFSET(D108,COUNTBLANK(D109:D113)+COUNTA(D109,D113),0). =A0This works =
well but
> makes updating the list difficult. =A0Is there a simpler way to achive th=
e same
> result?
>
> Thank you in advance

One possible solution is to create a dynamic range with a name and
then have a formula w/ =3Dmin(named_range) in cell d106
0
mick22306 (8)
5/21/2009 5:11:22 PM
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
--- 
"JGB" wrote:
> 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
> 
0
demechanik (4694)
5/22/2009 2:35:19 AM
Thank you Max it works a treat

"Max" wrote:

> 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
> --- 
> "JGB" wrote:
> > 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
> > 
0
jgb (10)
5/22/2009 8:57:00 PM
Thanks Joel I will try it out

"JoelS" wrote:

> On May 21, 11:04 am, JGB <J...@discussions.microsoft.com> wrote:
> > 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
> 
> One possible solution is to create a dynamic range with a name and
> then have a formula w/ =min(named_range) in cell d106
> 
0
jgb (10)
5/22/2009 8:58:01 PM
Welcome, glad to hear that.
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
--- 
"JGB" <JGB@discussions.microsoft.com> wrote in message 
news:69091AA2-5801-480B-981A-E634E4711AE3@microsoft.com...
> Thank you Max it works a treat


0
demechanik (4694)
5/22/2009 11:47:22 PM
Reply:

Similar Artilces:

Merging List w/ a Form
Can anyone assist me with merging a form created in Excel with a database (or list) also created in Excel? I know how to merge an Excel worksheet to be used as a database for Word in the Mail Merge command, but my FORM, with which I am trying to merge my database, was created in Excel and needs to remain under its format (not be converted to Word). I am filtering the information I need from the database to be imported to the Excel Form. Any help would be appreciated. Thank-you. If you're trying to display information from one record in the database, there are sample files here...

Dlookup minimum value
Hello. I'm trying to use Dlookup to get the minimum date from a table. I was trying to do the following command: mDate=Dlookup(min("dateField"),"tblName") Somehow the code points an error on"Min", saying that "Sub or Function not defined". Is it possible what i'm doing? Is there any other way instead of looping through all the records? Thanks. Luis Try DMin() instead of DLookup() -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rat...

Hiding OLD customers in list
I have a customer list (tblCustomers) And an Orders table (tblOrders) I want to have an admin area functionality that prunes (hides from display in any list or report) Customers from the list that have not placed an order in X number of days (Admin can enter the number of days in a text box) Solution does not have to be in days or in a text box. Could be several check boxes (30 days, 10 weeks, 2 years...). I am just looking for the simplest solution for us to limit the Customer list by last order date. Any help here will be appreciated. Thanks in advance If it were me, I would have a ...

Sharing tasks, group task list
I have a team of account people who assign tasks to a production team. I want to create a shared task list that I (and others) can assign tasks to my team that also updates me when progress on the tasks has been made (ex. marked complete). I know I can do this from my personal task list, but is there a way to have a shared task list where items can be assigned and will also be updated through exchange? Public folders maybe...? Using Outlook 2003 on exchange server. Thanks! Chris Set up a shared Mailbox and make Tasks there, Assign them to team members. In that Tasks folder, show t...

find data in multiple open workbooks
Im looking to find data in multiple workbooks that are all opened at once. Is there a way of doing this? ...

Assign Picklist value to another Picklist
I have 2 picklist (territorycode and Listado) and i want to add the value selected of territorycode picklist into listado picklist Could be something like this in the Onchange Event... crmForm.territorycode.value=crmForm.CFPListado.value; but didn`t work. Any suggestions? Thanks in advance. Daniel Rodriguez SMS - Argentina Try using the returnValue property, e.g.: crmForm.territorycode.returnValue=crmForm.CFPListado.returnValue; "Daniel Rodriguez" wrote: > I have 2 picklist (territorycode and Listado) and i want to add the value > selected of territorycode ...

can,t find my e-mail address
please help reply thank you "dc columbus robbins" <robbinsnest@localnet.com> wrote in message news:eJFE6ktBIHA.324@TK2MSFTNGP04.phx.gbl... > please help reply thank you This newsgroup is for questions and answers about Microsoft Access database software. You need to find a newsgroup for the software you are using. And, just for your information, you have to be a great deal more specific about what you are doing, and what is happening, if you expect anyone to be able to give you a helpful answer. "Can't find my e-mail address" doesn't tell ...

List of Differences Between Outlook 2000 and Outlook 2003
I need to spend 45 minutes introducing Outlook 2003 to my Outlook 2000 users. Can anyone provide a site with a succinct list (screenshots would help) of differences between Outlook 2000 and Outlook 2003? Many thanks. Tom Are you looking for screenshots of Outlook 2003 or ones comparing OL2000 and 2003? Wouldn't you be a more convincing teacher if used both and took the screenshots yourself? This would help you learn and understand the products better. (Vmware or virtual pc make this really easy and you could show them the differences live. :)) -- Diane Poremsky [MVP - Outlook] ...

Comparing Values In 2 Columns
Hi, i have 2 list of students names both of which are not up to date with each other and because of this one list has more students than the other. I want to search for students names and see if there is a match, if a match is found i need to copy the email address and paste it into the cell by the other name in the other list. See Below My spreadsheet has columns titled, (A)Display Name, (B)Display Email, (C)Sims Name and (D)Sims Email. I basically want to write a script that takes each individual display name in column A and searches in column C for an identical match, if a match is found i...

Contact List
How come whne I put in a contact in Outlook 2004, and then I add a fax #, it lists the contact twice on my main list? It's because there is no Outlook 2004. The standard behavior for the Outlook Address book is to display all electronic addresses (both fax and e-mail). That behavior cannot be changed. The only way to prevent the display of fax numbers is to store them in a different field or to disguise them (e.g., precede them with an alpha character) so that Outlook won't recognize them as phone numbers. There are also a number of utilities available that can do this for you automat...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

grabbing emails and paste into dist list?
Hi there, Okay, here's the scenario in Outlook 2003. I get an email from someone who has included about 50 important people in the cc field. I'd like to take all those email addresses and dump them into a distribution list in one fell swoop. So far, I hit "reply to all" and selected all of those cc email addresses, but I can not paste into a distribution list. How can I do this? Thanks, Gordon ...

value of value of a variable.
I tried searching, but no use! I have a Const NameA = "BLA BLA" I have a variable NameB Value of NameB is NameA. How do i get the text "BLA BLA" from NameB variable Is there anyway to do that? something like,, VALUE(NameB) Thanks & Regards Joe Hi Dim NameB as String NameB =3D NameA in a cell you could have range("A1").Value =3D NameB would now have content "BLA BLA" regards Paul On May 21, 12:35=A0pm, Joe <joe.varghese.j...@gmail.com> wrote: > I tried searching, but no use! > > I have a Const Name...

Filters, Can't Find Exception List In Rules
I'm trying to set up my filters for junk mail. I want junk mail with certain words in the subject sent to deleted folder. I found the filters.txt file and some of the words are already there. So apparently the filter isn't working. I tried to follow the directions on how to turn them on and they said to choose the exceptions entry in the rules wizard but I have no such choice. I know how to edit the filters.txt file but Outlook is not using it. Can anyone help? The Filters.txt file isn't actively used by Outlook -- it's really just a "readme" type file to sh...

Scrolling Tables list
When I use the scroll bar to move up and down the list of tables or queries etc, the list continues to vacillate after I stop sliding the bar; making it difficult to find the item I want to select. This seems to be worse with my new computer. Are there settings I should adjust. "Crop scout" <Crop scout@discussions.microsoft.com> kirjoitti viestiss´┐Ż:771C65DD-5D6E-47EF-B987-C797A680FB59@microsoft.com... > When I use the scroll bar to move up and down the list of tables or > queries > etc, the list continues to vacillate after I stop sliding the bar; makin...

Table-like Outline and Collumm that sums prevous values up
Hello The report I would like to have should be in a tabular form just like the doc document I have attached. However, until now I have not found out how to set the report into a tabular outline. It is quite uncomfortable and time intensive to set up this report by using lines and the different text fields. In addition, it will not look any good at the end.. Also, I would need a columm that is able to sum up the values before. Just like the "total" under each semester. Are the issues solvable? Greets, Hubertholz attached: http://rapidshare.com/files/35708241...

Creating a e-mail contact list which has "undisclosed recipients"
Can anyone help me with a contact list for my e-mail which does not disclose all recipients names. I would like to send an e-mail regarding company information to several people which answers questions, but I don't want everyone to know who I am addressing it to. You need enter the names under the Bcc option when you address the email. That way, each recipient will only see their own name, but the email will go to everyone. Actually, the recipients won't see their own name in the To field. They will only see what the sender puts there, if anything (some ISPs require that you...

Is it possible to find multiple data?
We have a small lotterysyndicate in work which is all logged onto an excel spreadsheet. Is it possible to search all numbers and all occurances at once, rather than individually? Ie - we have 15 lines of 6 numbers each - can i search for all 6 numbers drawn, across all 15 lines in one go? Im sure this must be possible - just dont know how. Thanks -- stewwie ------------------------------------------------------------------------ stewwie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37128 View this thread: http://www.excelforum.com/showthread.php?threadid...

Control Supported Device List for Active Sync
Hi I want to restrict what devices can connect to Active Sync - there is a built in list of supported devices which the service packs keep updating but is there a way to control this list yourself i.e. to only include two devices in the list? I want to control which devices meet the company standard and stop any which don't! More Info: Overview of mobile devices that are supported by Outlook Mobile Access in Exchange Server 2003 http://support.microsoft.com/Default.aspx?kbid=821835 Thanks Clive ...

how can I show repeating values in a chart?
I would like to show modes in the form of a pie chart but am not sure how. For example the number 73 comes up 3 times in a column on my spreadsheet, how can I show that compared to the number 50 which come up 2 times in the sheet? Thanks for the help Hi, You will need to compute those values using formula or a pivot table and then chart the results. Cheers Andy Cindy wrote: > I would like to show modes in the form of a pie chart but am not sure how. > For example the number 73 comes up 3 times in a column on my spreadsheet, how > can I show that compared to the number 50 whi...

Advanced Find should let me search records between two dates
When searching the system for records, many times I need to search for records that came in between two dates. For instance, I would like to be able to pull all records input in CRM between March 1 and March 15. In 3.0 you can only query specific dates like "Last X days," "Last X Months," "On," "On or After," etc., but you cannot search the date fields between two dates. The functionality was available in 1.2 but is not available in 3.0. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the...

Drop Down List for Chart
I have grouped my series into several groups. How do I display a drop down list including the groups which will dynamically identify which series to plot on the chart? For example: Group A = Series 1, Series 2, Series 3 Group B = Series 4, Series 5 Group C = Series 6 Drop down list includes values: "Group A", "Group B", "Group C" When Group A is selected within the drop down, Series 1, 2 and 3 are plotted on the chart. Do you mean you have to plot data, and have the chart move week to week? if so, easiest way is to use a hlookup/vlookup "savior1&quo...

hwo to recover personal folders and contact list??
I had office 2003 running on my C: drive and I had a major system crash so I backed up the entire C: drive to D: .. then I formatted and reinstalled windows XP pro and office 2003. can someone tell me where on the D: drive I woudld find the contact list and all my folders from outlook 2003 that I had running before? THanks.. Hi Dave, please look here, which datas are important for outlook http://home.arcor.de/andreas.roeder-privat/English%20Help.htm#18 If you search for the relevant files you could import them into your new Outlook! -- Gestern gings noch, ICH habe nichts gemacht! ;-) Brave...

Customize global adress list column in a new mail
Hi! (I have Outlook 2007 and Exchange 2007). when i create a new mail message, hit the "To" button, my global adress list window appear when column by default: Name, Title, business Phone, Location, E-mail address, Company and Alias. Those column from Active directory of course. My question is: can I add/remove column in this window? For example adding "department" from Active directory? thx Since this is customized by your Exchange Admin, I would start by asking him/her if it is possible. This question can most likely be answered in an Exchange or W...

finding common numbers in large lists
Hello, I have two large lists of numbers (on two seperate worksheets). By large, I mean that one list is 1000+ numbers, and the second list is 3000+ numbers (each number is in its own cell). The numbers are of the form: xxxx.xxx (that is, they are precise up to three places after the decimal) 1) Is there some quick way that I can find the numbers that are common to both lists? 2) Is there a way I can find the numbers in common to both lists using only the whole number portion (diregarding what comes after the decimal of each number)? Thanks! Hi see: http://www.cpearson.com/excel/duplic...