Get Value of First visible cell using Auto Filter

[realniceguy5000]

Hi,

I'm using auto filter to gather information from a giant list. I'm able to use Subtotal function in Row(1) )to gather all the numbers for a person.What I havent been able to do is figue out a way to get the value of the person that I am auto filtering.

Example
Row 1 contains my subtotal formula's
Row 2 Header Row
Row 3 Auto Filter Buttons
Row 4 All The Data

Column(A4:A65000)has the names of the people So when I use the Auto filter by Name how can I get the first visible cell name to show in (A1)?

Thanks again for your help.

Mike

[JeanRage]

Hi,

Attached is a sample file ... with an array formula ...

HTH

[realniceguy5000]

Thanks JeanRage,

That's what I was looking for.

It seems to run rather slow at least in my situation, I did change the 65000 to 20000 which help speed things up a little. Less cells to look at But it still locks up for a little.

Anyway thank you for your help, Mike

Please Login or Register  to view this content.

[DonkeyOte]

It will be a slow, it's a Volatile array looking at thousands of rows (and given it's volatile it will recalculate (frequently)).

You may find it a little quicker to add a calc at source, eg:

Z4: =SUBTOTAL(3,A4)
copied down for all rows

Then

A1: =INDEX(A4:A2000,MATCH(1,Z4:Z2000,0))


Note that running filters is a Volatile action in itself, for more info. on Volatility see link in sig.

[realniceguy5000]

It will be a slow, it's a Volatile array looking at thousands of rows (and given it's volatile it will recalculate (frequently)).

You may find it a little quicker to add a calc at source, eg:

Z4: =SUBTOTAL(3,A4)
copied down for all rows

Then

A1: =INDEX(A4:A2000,MATCH(1,Z4:Z2000,0))


Note that running filters is a Volatile action in itself, for more info. on Volatility see link in sig.

I understand now,what a differance that change made. That works great!!!!

Thank You for the follow up...

Mike

[kawai54]

Thank you, JeanRage!! Just what I needed. :-)