Results 1 to 6 of 6

20100106, 03:54 #1
 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

20100106, 05:19 #2
 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

20100106, 07:21 #3
 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

20100106, 07:47 #4
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts

20100106, 12:00 #5
 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: =C159This ecopost is made of recycled electrons

20100106, 20:19 #6
 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