#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,722
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default 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.




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use INDEX? Eric Excel Discussion (Misc queries) 1 July 16th 07 04:46 PM
Chart axes color index vs font color index [email protected] Charts and Charting in Excel 4 December 7th 06 05:05 PM
How do I pull the col. index value as well as row index value Vikram Dhemare Excel Discussion (Misc queries) 1 March 29th 06 07:48 AM
index/index Bobby Excel Worksheet Functions 2 January 30th 06 06:53 PM
how do i get the help index EXCEL2002 New Users to Excel 4 December 25th 05 04:16 AM


All times are GMT +1. The time now is 01:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"