Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter setting
I have a autofiltered data range from which I calculate a number of subtotals.
I.e: column 2 filtered to show "tool A" - how do I get the value "tool A" to a cell outside the filtered data range by using a worksheet function? rgds! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter setting
"dNd BdN" wrote:
I have a autofiltered data range from which I calculate a number of subtotals. I.e: column 2 filtered to show "tool A" - how do I get the value "tool A" to a cell outside the filtered data range by using a worksheet function? Try Tom Ogilvy's ShowFilter UDF (below) To implement the UDF: Press Alt+F11 to go to VBE Click Insert Module Copy and paste the UDF (below) into the white space on the right [everything within the dotted lines] Press Alt+Q to get back to Excel In Excel, assuming autofilter is applied on cols A to C in row1 To use Tom's UDF, put in say, K1: =showfilter(A1), copy K1 to M1. K1:M1 will display the autofilter selections made in A1:C1 '--- 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 Application.Volatile 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 '--- -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter setting
Works great - thanks!
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter setting
welcome, great to hear that !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dNd BdN" wrote in message ... Works great - thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting and filter Listbox data | Excel Discussion (Misc queries) | |||
Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) | |||
Custom Auto Filter default setting should be contains | Excel Worksheet Functions | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
Setting default pivot table field setting to "sum" | Excel Discussion (Misc queries) |