Previous visible cell


F

Fred

Hi,

I have a worksheet using an autofilter and I need to get the value of the
previous visible cell once the filter is applied.

Assuming my formula is in cell A1000 then is there a worksheet function(s)
that can give me the value of the previous visible cell which will change
depending on what filter criteria I have.

Thanks for any help

Fred
 
Ad

Advertisements

B

Bernie Deitrick

Fred,

In an otherwise blank column, insert a formula like this (in cell A2, for example, column B being
part of the filtered range):

=SUBTOTAL(3,$B$1:B2)

and then copy down to match column B.

Then, to get the last filtered item from column B, use

=VLOOKUP(MAX(A1:A19),A1:B1000,2,FALSE)

The second to last, use

=VLOOKUP(MAX(A1:A19)-1,A1:B1000,2,FALSE)

etc.

HTH,
Bernie
MS Excel MVP
 
Ad

Advertisements

F

Fred

Thanks Bernie,

Worked well..

Bernie Deitrick said:
Fred,

In an otherwise blank column, insert a formula like this (in cell A2, for
example, column B being part of the filtered range):

=SUBTOTAL(3,$B$1:B2)

and then copy down to match column B.

Then, to get the last filtered item from column B, use

=VLOOKUP(MAX(A1:A19),A1:B1000,2,FALSE)

The second to last, use

=VLOOKUP(MAX(A1:A19)-1,A1:B1000,2,FALSE)

etc.

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top