Formula for displaying first and last filtered values?

[Big Lar]

Sheet1 Row 1 = Column headers
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
After Sheet1 AutoFilter <o></o>
<o></o>
Sheet2 A1= First filtered value from Sheet1 Column A<o></o>
Sheet2 B1= Last filtered value from Sheet1 Column A<o></o>

Thanks for your participation.

 

[Domenic]

Try the following...

First:

=INDEX(A2:A100,MATCH(1,SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),0))

...confirmed with CONTROL+SHIFT+ENTER.

Last:

=LOOKUP(2,1/(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1))),A2:A100)

Edit: Adjust the range and add the appropriate sheet reference...

 

[Big Lar]

Domenic...you following me? LOL

The data is on Sheet1.
I'm trying to display first and last filtered values on Sheet2. Isn't there a missing reference in those formula?

Plus, my Sheet1 has potential for several thousand rows of data.

Thanks for you help.

 

[Domenic]

Domenic...you following me? LOL

The data is on Sheet1.
I'm trying to display first and last filtered values on Sheet2. Isn't there a missing reference in those formula?

I guess you missed my edit.

Sheet2!A1:

=INDEX(Sheet1!A2:A100,MATCH(1,SUBTOTAL(3,OFFSET(Sheet1!A2:A100,ROW(Sheet1!A2:A100)-ROW(Sheet1!A2),0,1)),0))

...confirmed with CONTROL+SHIFT+ENTER.

Sheet2!B1:

=LOOKUP(2,1/(SUBTOTAL(3,OFFSET(Sheet1!A2:A100,ROW(Sheet1!A2:A100)-ROW(Sheet1!A2),0,1))),Sheet1!A2:A100)

Plus, my Sheet1 has potential for several thousand rows of data.

You'll need to adjust the range, accordingly. If the range will keep increasing in size, convert your data into either a list (Excel 2003, Data > List > Create List) or table (Excel 2007 or later, Insert > Tables > Table). Then, your references will automatically adjust as data is added/removed.

Thanks for you help.

You're very welcome!

 

[Big Lar]

The LOOKUP works perfectly, but...

=INDEX(Sheet1!A2:A100,MATCH(1,SUBTOTAL(3,OFFSET(Sheet1!A2:A100,ROW(Sheet1!A2:A100)-ROW(Sheet1!A2),0,1)),0))

results in #N/A...I can't figure out why.

(Too many 0,1))-,0) for me maybe?)

You=Genius
Me=NotSoMuch

 

[Domenic]

Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER?

 

[Big Lar]

Uh...no

Your status remains...as does mine!

Thx

 

[Domenic]

Uh...no

Your status remains...as does mine!

Thx

You're welcome!