Index

  • Thread starter Thread starter Need_Help
  • Start date Start date
N

Need_Help

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.
 
Try this array formula** :

Assume the full unfiltered range is A2:A15

=INDEX(Sheet1!A2:A15,MATCH(1,SUBTOTAL(3,OFFSET(Sheet1!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.
 
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.
 
This worked. Thank you very much.

T. Valko said:
Try this array formula** :

Assume the full unfiltered range is A2:A15

=INDEX(Sheet1!A2:A15,MATCH(1,SUBTOTAL(3,OFFSET(Sheet1!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.
Click to expand...
 
Back
Top