How to reference to top row of filtered data / then formula to reference the second row...

Hi Guys!

This question was posted and is similar to what i was after,

I am using filters and want to return the value from the top row of the filtered data into another cell. I can't reference to the top row, as when the rows hide when I use another filter, it returns the incorrect value. I'm sure this has a simple solution - I just can't think it!

Answer which works for me,

Hi,



Say your filtered range is A1:A20 with a header in A1. This ARRAY formula returns the top visible row below the header



=INDIRECT("A"&MIN(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1)),ROW(A2:A20))))



To Adapt this you will need to change the column; the first A, and the range. With the range remember it doesn't include the cell with the filter arrow in.



This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

I now need the formula to return the Second Row down not the first.

How do i achieve this? I have tried altering the Parameters within the Code but alas... nothing