Index
 

I'm working with two worksheets. Worksheet1, in column A I have years that i
use a filter on. What I want to be able to do is after I filter on the years
in worksheet1 is show what year is being filtered in worksheet2.

before Filter After Filtering on 2006
2006 2006
2006 2006
2007
2007
2007

I want to show 2006 in worksheet2 cell a1

Please help.

Index
 

Try this array formula** :

Assume the full unfiltered range is A2:A15

=INDEX(Sheet1!A2:A15,MATCH(1,SUBTOTAL(3,OFFSET(She et1!A2:A15,,,ROW(Sheet1!A2:A15)-MIN(ROW(Sheet1!A2:A15))+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Need_Help" wrote in message
...
I'm working with two worksheets. Worksheet1, in column A I have years that
i
use a filter on. What I want to be able to do is after I filter on the
years
in worksheet1 is show what year is being filtered in worksheet2.

before Filter After Filtering on 2006
2006 2006
2006 2006
2007
2007
2007

I want to show 2006 in worksheet2 cell a1

Please help.

Index
 

Since you're using years (numbers as opposed to text) you can use this trick.
In Sheet2, A1

=SUBTOTAL(4,'Sheet1'!A:A)

Not a fool-proof solution, but for the manner you describe, it should work.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Need_Help" wrote:

I'm working with two worksheets. Worksheet1, in column A I have years that i
use a filter on. What I want to be able to do is after I filter on the years
in worksheet1 is show what year is being filtered in worksheet2.

before Filter After Filtering on 2006
2006 2006
2006 2006
2007
2007
2007

I want to show 2006 in worksheet2 cell a1

Please help.

Index
 

This worked. Thank you very much.

"T. Valko" wrote:

Try this array formula** :

Assume the full unfiltered range is A2:A15

=INDEX(Sheet1!A2:A15,MATCH(1,SUBTOTAL(3,OFFSET(She et1!A2:A15,,,ROW(Sheet1!A2:A15)-MIN(ROW(Sheet1!A2:A15))+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Need_Help" wrote in message
...
I'm working with two worksheets. Worksheet1, in column A I have years that
i
use a filter on. What I want to be able to do is after I filter on the
years
in worksheet1 is show what year is being filtered in worksheet2.

before Filter After Filtering on 2006
2006 2006
2006 2006
2007
2007
2007

I want to show 2006 in worksheet2 cell a1

Please help.

Index
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Need_Help" wrote in message
...
This worked. Thank you very much.

"T. Valko" wrote:

Try this array formula** :

Assume the full unfiltered range is A2:A15

=INDEX(Sheet1!A2:A15,MATCH(1,SUBTOTAL(3,OFFSET(She et1!A2:A15,,,ROW(Sheet1!A2:A15)-MIN(ROW(Sheet1!A2:A15))+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Need_Help" wrote in message
...
I'm working with two worksheets. Worksheet1, in column A I have years
that
i
use a filter on. What I want to be able to do is after I filter on the
years
in worksheet1 is show what year is being filtered in worksheet2.

before Filter After Filtering on 2006
2006 2006
2006 2006
2007
2007
2007

I want to show 2006 in worksheet2 cell a1

Please help.