Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    I have a spreadheet the contains either a N or U in column C from row 8 unwards. I use a filter to show all the U's or N's.

    I need a formula that will extract the last item in column C from row 8 onwards and placing this in H159

    Your assistance will be most appreciated

    Thanks


    Howard

  2. #2
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You probably need a code to place the input directly in H159
    OR
    =LOOKUP("Z",C8:C65536) in H159 will return the last value from the specified range.
    Regards
    Prasad

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Prasad

    Thanks for the help. does the "Z" in the formula represent the last item in the lookup or what does it represent?

    Regards

    Howard

  4. #4
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HowardC View Post
    Hi Prasad

    Thanks for the help. does the "Z" in the formula represent the last item in the lookup or what does it represent?

    Regards

    Howard
    Since there are only 2 character in your data, I use "Z" to make excel understand to look this first & if not found, put the last value from the range.
    Regards
    Prasad

  5. #5
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    For me, the question is slightly different: how to return the content of the last visible row of a filtered area. If this is the correct question, then I would suggest the following:
    1) Create a new column (that you can hide later on), with the formula =ROW() on each row. It simply returns the row number. let's say it is the column D.
    2) On the cell D159, put the formula =SUBTOTAL(4, D1158) which returns the maximum of the filtered value of the column D. This means it returns the number of the last visible row of the filtered range.
    3) In the cell A159, put the formula =OFFSET(A$1, $D$159 - 1, 0) which will return the value in the column A of the last filtered row. Extend the formula to all the useful cells of the row 159.
    4) Finally, simply put in H159 the formula: =C159
    This eco-post is made of recycled electrons

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Finding the last filtered entry is difficult and possibly easier with a custom function
    However,IF you want to find the entry in the last cell in say column C that has either
    a U or an N in it then putting this formula somewhere would work I think
    (Well it did for me anyway)

    NOTE..These are ARRAY Formulas and so must be entered with

    SHIFT CTRL ENTER

    =INDEX(C8:C2000,MAX(IF(C8:C2000="U",ROW(C8:C2000), IF(C8:C2000="N",ROW(C8:C2000),0))),1)

    You would need to adjust the end row to allow for your data

    If you wanted to make it flexible to parameters fore the U and N then replace these with
    Absolute Cell Refs to where the U and N could be types

    eg

    =INDEX(C8:C2000,MAX(IF(C8:C2000=$H$157,ROW(C8:C200 0),IF(C8:C2000=$H$158,ROW(C8:C2000),0))),1)
    Andrew

Posting Permissions

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