|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 01:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com