Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use INDEX? | Excel Discussion (Misc queries) | |||
Chart axes color index vs font color index | Charts and Charting in Excel | |||
How do I pull the col. index value as well as row index value | Excel Discussion (Misc queries) | |||
index/index | Excel Worksheet Functions | |||
how do i get the help index | New Users to Excel |