I have a formula
=INDEX($B$14:$B$16,SMALL(IF($A$14:$A$16<>"",
ROW($A$14:$A$16)-ROW($A$14)+1),SUBTOTAL(103,$C$5:C5)))
I am trying to get the next match on filtered rows using the subtotal function for the K part. The problem is that when I unfilter, it returns the incorrect result as the K has changed because it is adding all the rows. Is there a way to change the K part so the result will remain the same for filtered and unfiltered rows and making sure it also increments by 1 as well?
I will be using Excel 2007.
Bookmarks