Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    This ones a cracker! (2000/03)

    I have used this formula: =LOOKUP(9.99999999999999E+307,B13:B438) to find and display the last cell entry in (in this case) column B. Column A has the days of the year from 1 Jan to 31 Dec 05.

    Is there a formula or array, that will give the date opposite the last cell entry in column B?(or C, D E...etc). I've tried a VLOOKUP and putting another column of dates on the right side of the info, MATCH, INDEX, etc, but I'm guessing this one will be a special one.

    Thank you

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: This ones a cracker! (2000/03)

    This finds the last entry in Column A

    {=INDIRECT(ADDRESS(MAX((A1:A100<>"")*ROW(A1:A100)) ,COLUMN(A1:A100),4))}

    and this the last in Column B

    {=INDIRECT(ADDRESS(MAX((A1:A100<>"")*ROW(A1:A100)) ,COLUMN(A1:A100)+1,4))}


    Obviously change the range to suit your needs
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: This ones a cracker! (2000/03)

    Try
    <code>
    =INDEX(A13:A438,MATCH(9.99999999999999E+307,B13:B4 38))
    </code>
    BTW why B13:B438? It contains426 cells, not 365...

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This ones a cracker! (2000/03)

    You can compine the Offset and Match functions for this one:

    =OFFSET(B3,MATCH(LOOKUP(9.99999999999999E+307,B13: B438),B13:B438)-1,<font color=red>1</font color=red>,1,1)

    Change the <font color=red>red 1</font color=red> to the number of columns to the right of column B you want returned.

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This ones a cracker! (2000/03)

    Hans

    I went back 2 months in addition to the 12 months to test for other conditions in the sheet. I can send the sheet if you are curious.

    Now to work on the suggestions!!!

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This ones a cracker! (2000/03)

    Jezza,
    That works for finding the last populated cell in the column, which for column A, was 31-12-05 and for column B was 0:00 which is a time cell in the format [h]:mm. I was looking for the last entry in B and the date in A which corresponded. But thanks for taking the time and showing us all an interesting formula.

    Mbarron,
    I have puzzled over this one, and have not grasped the way it works; (then again, grasped never was my strong point!) but, thank you for taking the time to reply and your knowledge.

    Hans,
    That's the one! It worked! Just made the A column ref absolute and it does what it says on the tin. (AKA WOPR).

    Many thanks everyone.

Posting Permissions

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