function to reference value selected with autofilter
A single column value has been selected with the autofilter at the top of the
column. Is there a way to store that selected value in a cell to reference it with functions? |
function to reference value selected with autofilter
This array formula will return the *first* item in the range D2:D15.
=INDEX(D2:D15,MATCH(1,SUBTOTAL(3,OFFSET(D2:D15,,,R OW(D2:D15)-MIN(ROW(D2:D15))+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 "cm" wrote in message ... A single column value has been selected with the autofilter at the top of the column. Is there a way to store that selected value in a cell to reference it with functions? |
function to reference value selected with autofilter
I got this function from T.Valko a while back:
=INDEX(B4:B620,MATCH(1,(SUBTOTAL(3,OFFSET(B4:B620, ROW(B4:B620)-MIN(ROW(B4:B620)),0,1)))*(B4:B620<""),0)) Hope that does what you want. Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "T. Valko" wrote: This array formula will return the *first* item in the range D2:D15. =INDEX(D2:D15,MATCH(1,SUBTOTAL(3,OFFSET(D2:D15,,,R OW(D2:D15)-MIN(ROW(D2:D15))+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 "cm" wrote in message ... A single column value has been selected with the autofilter at the top of the column. Is there a way to store that selected value in a cell to reference it with functions? |
All times are GMT +1. The time now is 04:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com