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! |
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 --- |
Filter setting
Works great - thanks!
|
Filter setting
welcome, great to hear that !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dNd BdN" wrote in message ... Works great - thanks! |
All times are GMT +1. The time now is 04:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com