How do I avoid referencing hidden values in formulas like OFFSET?

[K]

I am using autofilter and each time I switch to the next entry I need to
capture the last value in a certain column to use as part of a new formula.
Let's suppose my filter shows me my headings in row one, data in rows 11-20,
and then first blank row (row 3100) below all the hidden rows. I was trying
to use OFFSET, with a cell in row 3100 as my reference in order to see data
in row 20, but instead I am getting row 3099. I realize that functions like
SUBTOTAL ignore hidden rows, but there are only 11 different applications of
SUBTOTAL. How can I add this useful feature of SUBTOTAL to OFFSET, or
something similar?

 

[Harlan Grove]

K wrote...

I am using autofilter and each time I switch to the next entry I need to
capture the last value in a certain column to use as part of a new formula.
Let's suppose my filter shows me my headings in row one, data in rows 11-20,
and then first blank row (row 3100) below all the hidden rows. I was trying
to use OFFSET, with a cell in row 3100 as my reference in order to see data
in row 20, but instead I am getting row 3099. I realize that functions like
SUBTOTAL ignore hidden rows, but there are only 11 different applications of
SUBTOTAL. How can I add this useful feature of SUBTOTAL to OFFSET, or
something similar?

Add a column to the table that shows the row numbers, then use
SUBTOTAL(4,NewCol) to get the row number of the last visible row in the
filtered table. Use that with INDEX.

 

[K]

K wrote...

Add a column to the table that shows the row numbers, then use
SUBTOTAL(4,NewCol) to get the row number of the last visible row in the
filtered table. Use that with INDEX.

Thank you. It worked!