Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: How do I.....

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a logbook in excel. Included is a column with airplane names in it, and a column with dates when i flew those airplanes. What i'm looking for is a function that will tell me the date that i last flew a specific airplane.

    For example, given

    column 1 = piper, piper, zlin, piper, zlin
    column 2 = may 10, may 12, may 13, may 15, may 16

    I want a function that will look through the list and tell me I last flew a piper on may 15.

    Thanks!

    - Adam

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-18 17:44, adamdalz wrote:
    I have a logbook in excel. Included is a column with airplane names in it, and a column with dates when i flew those airplanes. What i'm looking for is a function that will tell me the date that i last flew a specific airplane.

    For example, given

    column 1 = piper, piper, zlin, piper, zlin
    column 2 = may 10, may 12, may 13, may 15, may 16

    I want a function that will look through the list and tell me I last flew a piper on may 15.

    Thanks!

    - Adam
    Hi Adam:
    Welcome to the board. One of the ways you can this is by using the DMAX function. Let us say your data is set up as follows
    cell A1 ... Plane
    cell A2 ... piper
    cell A3 ... piper
    cell A4 ... zin
    cell A5 ... piper
    cell a6 ... zin

    cell B1 ... fly_date
    cell B2 ... 5/10
    cell B3 ... 5/12
    cell B4 ... 5/13
    cell B5 ... 5/15
    cell B6 ... 5/16

    then you want to put your criteria in cells D1:D2
    in cell D1 ... Plane
    in cell D2 ... piper

    then the formula for the last date you flew piper:

    =DMAX(A1:B6,2,D1:D2) ... giving you 5/15

    HTH

    Please post back if it works for you ... otherwise explain a little further and let us take it from there!


    _________________
    Yogi Anand
    Edit: Deleted reference to inactive web site from signature line

    [ This Message was edited by: Yogi Anand on 2003-01-19 18:30 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    heres a quick macro, but it looks like yogi's solution is probably better, and easier So many functions, so little time to learn them all

    Sub Fly_search()

    planename = InputBox("Please input the name of the plane you are looking for:")
    Range("A1").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
    , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom

    For Each i In Range("A1", Range("A65536").End(xlUp))
    If i.Value = planename Then
    If i.Offset(1, 0) <> planename Then flydate = i.Offset(0, 1)
    End If
    Next i
    If flydate = "" Then
    MsgBox ("plane not found")
    Else
    MsgBox ("The last date " & planename & " was flown was " & flydate)
    End If
    End Sub


    [ This Message was edited by: robfo0 on 2002-04-18 19:52 ]

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm "adamdalz" friend...

    this past weekend I was trying to get the DMAX function to work but I just couldn't get the fields in the right order or something.

    the DMAX function is a bit shifty.

    Good to know I was on the right track though.

    Now I undersand that in DMAX the first is the range to check, and the third is the criteria... what is the second field, (The '2' in this case)?

    [ This Message was edited by: Ice5 on 2002-04-18 21:10 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    the 2 is the column where the values are which you want to return, in this case you would have the database defined as A1:B6, B or the 2nd column contains the dates which you are looking for. if it were A1:C6, and c contained other data, and you were looking for something in that column, you would have a 3 instead of 2

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ice5:
    To elaborate what robfo0 stated, 2 in the formula

    =DMAX(A1:B6,2,D1:D2) is the number of column in the field in which the value being sought lies. I could also have substituted the field name in the formula instead of the associated column number, so the formula would have been...

    =DMAX(A1:B6,"fly_date",D1:D2) and the result would have been the same 5/15

    Regards!

    Yogi anand

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •