Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting data from a filtered list
Hi everyone,
I'm trying to get the value of a filter I set in a list. It's basically a listing of names and data relating to these names. I apply a filter on the names column so that it shows only the rows that contains that one specific name and I would like to insert that specific name in another cell. The problem is that I don't know how to refer to what I selected in the filter. Anyway you can help me? Thank you! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting data from a filtered list
This is from a Tom Ogilvy post:
http://j-walk.com/ss/excel/usertips/tip044.htm to get it to refresh: =FilterCriteria(B5)&left(Subtotal(9,B5:B200),0) this is one I wrote back in 2000 Here is a user defined function that will display the criteria in a cell: Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function =ShowFilter(B5)&left(Subtotal(9,B5:B200),0) would show the filter for column 2 I usually put these functions in cells above the filter ============== If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gaetan wrote: Hi everyone, I'm trying to get the value of a filter I set in a list. It's basically a listing of names and data relating to these names. I apply a filter on the names column so that it shows only the rows that contains that one specific name and I would like to insert that specific name in another cell. The problem is that I don't know how to refer to what I selected in the filter. Anyway you can help me? Thank you! -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting data from a filtered list
This works great!
Thanks! "Dave Peterson" wrote: This is from a Tom Ogilvy post: http://j-walk.com/ss/excel/usertips/tip044.htm to get it to refresh: =FilterCriteria(B5)&left(Subtotal(9,B5:B200),0) this is one I wrote back in 2000 Here is a user defined function that will display the criteria in a cell: Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function =ShowFilter(B5)&left(Subtotal(9,B5:B200),0) would show the filter for column 2 I usually put these functions in cells above the filter ============== If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Gaetan wrote: Hi everyone, I'm trying to get the value of a filter I set in a list. It's basically a listing of names and data relating to these names. I apply a filter on the names column so that it shows only the rows that contains that one specific name and I would like to insert that specific name in another cell. The problem is that I don't know how to refer to what I selected in the filter. Anyway you can help me? Thank you! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grabbing the filtered data in a list? | Excel Worksheet Functions | |||
Grabbing the filtered data in a list? | Excel Worksheet Functions | |||
Autocopy of data filtered by drop down list | Excel Worksheet Functions | |||
Select data in filtered list | Excel Discussion (Misc queries) | |||
Drop down list from filtered data | Excel Discussion (Misc queries) |