| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referncing Rows of Filtered Data
I need an Index() function that works against filtered data. I have filtered
data and I want to be able to reference row 3, column 4 of the filtered data only using the Index() funtion. The regular Index () function goes against all data, even rows hiden by the filter. Can any provide a solution? Thanks, -Mark |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referncing Rows of Filtered Data
Mark,
I think you would be better telling us what you are trying to do. Are these rows visible or hidden? To work with visible rows in a filtered range have a look at 'Subtotal' in help. Mike "Mark T." wrote: I need an Index() function that works against filtered data. I have filtered data and I want to be able to reference row 3, column 4 of the filtered data only using the Index() funtion. The regular Index () function goes against all data, even rows hiden by the filter. Can any provide a solution? Thanks, -Mark |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referncing Rows of Filtered Data
Assume the unfiltered range is A2:F15.
The 4th column would be column D so this formula will return what's on the 3 visible row from column D (assuming there are no empty cells within the filtered range of column D). Array entered** : =INDEX(D2:D15,SMALL(IF((SUBTOTAL(3,OFFSET(D2:D15,R OW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15),(SUBTOTAL(3,OFF SET(D2:D15,ROW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1) If there aren't 3 visible rows then you'll get an error. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mark T." wrote in message ... I need an Index() function that works against filtered data. I have filtered data and I want to be able to reference row 3, column 4 of the filtered data only using the Index() funtion. The regular Index () function goes against all data, even rows hiden by the filter. Can any provide a solution? Thanks, -Mark |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referncing Rows of Filtered Data
I believe you can shorten that to:
=INDEX(D2:D15,SMALL(IF(SUBTOTAL(3,OFFSET(D2:D15,RO W(D2:D15)-MIN(ROW(D2:D15)),,1)),ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1) "T. Valko" wrote: Assume the unfiltered range is A2:F15. The 4th column would be column D so this formula will return what's on the 3 visible row from column D (assuming there are no empty cells within the filtered range of column D). Array entered** : =INDEX(D2:D15,SMALL(IF((SUBTOTAL(3,OFFSET(D2:D15,R OW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15),(SUBTOTAL(3,OFF SET(D2:D15,ROW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1) If there aren't 3 visible rows then you'll get an error. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mark T." wrote in message ... I need an Index() function that works against filtered data. I have filtered data and I want to be able to reference row 3, column 4 of the filtered data only using the Index() funtion. The regular Index () function goes against all data, even rows hiden by the filter. Can any provide a solution? Thanks, -Mark |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referncing Rows of Filtered Data
I believe you can shorten that
Yep, it cleans up nicely! -- Biff Microsoft Excel MVP "JMB" wrote in message ... I believe you can shorten that to: =INDEX(D2:D15,SMALL(IF(SUBTOTAL(3,OFFSET(D2:D15,RO W(D2:D15)-MIN(ROW(D2:D15)),,1)),ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1) "T. Valko" wrote: Assume the unfiltered range is A2:F15. The 4th column would be column D so this formula will return what's on the 3 visible row from column D (assuming there are no empty cells within the filtered range of column D). Array entered** : =INDEX(D2:D15,SMALL(IF((SUBTOTAL(3,OFFSET(D2:D15,R OW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15),(SUBTOTAL(3,OFF SET(D2:D15,ROW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1) If there aren't 3 visible rows then you'll get an error. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mark T." wrote in message ... I need an Index() function that works against filtered data. I have filtered data and I want to be able to reference row 3, column 4 of the filtered data only using the Index() funtion. The regular Index () function goes against all data, even rows hiden by the filter. Can any provide a solution? Thanks, -Mark |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referncing Rows of Filtered Data
Thanks so much. I'll be putting this in place today!!
-Mark "T. Valko" wrote: I believe you can shorten that Yep, it cleans up nicely! -- Biff Microsoft Excel MVP "JMB" wrote in message ... I believe you can shorten that to: =INDEX(D2:D15,SMALL(IF(SUBTOTAL(3,OFFSET(D2:D15,RO W(D2:D15)-MIN(ROW(D2:D15)),,1)),ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1) "T. Valko" wrote: Assume the unfiltered range is A2:F15. The 4th column would be column D so this formula will return what's on the 3 visible row from column D (assuming there are no empty cells within the filtered range of column D). Array entered** : =INDEX(D2:D15,SMALL(IF((SUBTOTAL(3,OFFSET(D2:D15,R OW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15),(SUBTOTAL(3,OFF SET(D2:D15,ROW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1) If there aren't 3 visible rows then you'll get an error. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mark T." wrote in message ... I need an Index() function that works against filtered data. I have filtered data and I want to be able to reference row 3, column 4 of the filtered data only using the Index() funtion. The regular Index () function goes against all data, even rows hiden by the filter. Can any provide a solution? Thanks, -Mark |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referncing Rows of Filtered Data
You're welcome!
-- Biff Microsoft Excel MVP "Mark T." wrote in message ... Thanks so much. I'll be putting this in place today!! -Mark "T. Valko" wrote: I believe you can shorten that Yep, it cleans up nicely! -- Biff Microsoft Excel MVP "JMB" wrote in message ... I believe you can shorten that to: =INDEX(D2:D15,SMALL(IF(SUBTOTAL(3,OFFSET(D2:D15,RO W(D2:D15)-MIN(ROW(D2:D15)),,1)),ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1) "T. Valko" wrote: Assume the unfiltered range is A2:F15. The 4th column would be column D so this formula will return what's on the 3 visible row from column D (assuming there are no empty cells within the filtered range of column D). Array entered** : =INDEX(D2:D15,SMALL(IF((SUBTOTAL(3,OFFSET(D2:D15,R OW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15),(SUBTOTAL(3,OFF SET(D2:D15,ROW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1) If there aren't 3 visible rows then you'll get an error. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mark T." wrote in message ... I need an Index() function that works against filtered data. I have filtered data and I want to be able to reference row 3, column 4 of the filtered data only using the Index() funtion. The regular Index () function goes against all data, even rows hiden by the filter. Can any provide a solution? Thanks, -Mark |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referncing Rows of Filtered Data
Or even:
=INDEX(D2:D15,MATCH(3,SUBTOTAL(3,OFFSET(D2:D15,,,R OW(D2:D15)-MIN(ROW(D2:D15))+1)),0)) "JMB" wrote: I believe you can shorten that to: =INDEX(D2:D15,SMALL(IF(SUBTOTAL(3,OFFSET(D2:D15,RO W(D2:D15)-MIN(ROW(D2:D15)),,1)),ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1) "T. Valko" wrote: Assume the unfiltered range is A2:F15. The 4th column would be column D so this formula will return what's on the 3 visible row from column D (assuming there are no empty cells within the filtered range of column D). Array entered** : =INDEX(D2:D15,SMALL(IF((SUBTOTAL(3,OFFSET(D2:D15,R OW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15),(SUBTOTAL(3,OFF SET(D2:D15,ROW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1) If there aren't 3 visible rows then you'll get an error. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mark T." wrote in message ... I need an Index() function that works against filtered data. I have filtered data and I want to be able to reference row 3, column 4 of the filtered data only using the Index() funtion. The regular Index () function goes against all data, even rows hiden by the filter. Can any provide a solution? Thanks, -Mark |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referncing Rows of Filtered Data
Very nice - I'll have to try to remember that.
"Lori" wrote: Or even: =INDEX(D2:D15,MATCH(3,SUBTOTAL(3,OFFSET(D2:D15,,,R OW(D2:D15)-MIN(ROW(D2:D15))+1)),0)) "JMB" wrote: I believe you can shorten that to: =INDEX(D2:D15,SMALL(IF(SUBTOTAL(3,OFFSET(D2:D15,RO W(D2:D15)-MIN(ROW(D2:D15)),,1)),ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1) "T. Valko" wrote: Assume the unfiltered range is A2:F15. The 4th column would be column D so this formula will return what's on the 3 visible row from column D (assuming there are no empty cells within the filtered range of column D). Array entered** : =INDEX(D2:D15,SMALL(IF((SUBTOTAL(3,OFFSET(D2:D15,R OW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15),(SUBTOTAL(3,OFF SET(D2:D15,ROW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1) If there aren't 3 visible rows then you'll get an error. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mark T." wrote in message ... I need an Index() function that works against filtered data. I have filtered data and I want to be able to reference row 3, column 4 of the filtered data only using the Index() funtion. The regular Index () function goes against all data, even rows hiden by the filter. Can any provide a solution? Thanks, -Mark |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
| Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
| How to number rows after data has been filtered? | Excel Discussion (Misc queries) | |||
| Getpivotdata - referncing a data_field resulting in "REF! | Excel Worksheet Functions | |||
| concatenate cell and referncing afterwads | Excel Discussion (Misc queries) | |||
Linear Mode
