Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
a filter question
I am using the autofilter and cannot figure out a way to do this...
In Column A4:A500, I have 400+ rows with 3 distinct names in the column; "A","B","C"... When I filter this column by "A" I would like cell A2 to tell me that name; "A". If by "B" then "B" etc... If it is not filtered then tell me "All". I then will use this name on another sheet & cell in the workbook. Any help is appreciated! Hans |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
a filter question
From Tom Ogilvy:
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 " wrote: I am using the autofilter and cannot figure out a way to do this... In Column A4:A500, I have 400+ rows with 3 distinct names in the column; "A","B","C"... When I filter this column by "A" I would like cell A2 to tell me that name; "A". If by "B" then "B" etc... If it is not filtered then tell me "All". I then will use this name on another sheet & cell in the workbook. Any help is appreciated! Hans -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
a filter question
Dave,
You are the man...both work absolutley WONDERFUL! Thanks alots! Hans |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
a filter question
Tom's the man!
(I just copied|pasted--same as what you did <vbg!) " wrote: Dave, You are the man...both work absolutley WONDERFUL! Thanks alots! Hans -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Filter | Excel Discussion (Misc queries) | |||
Resetting the Filter Function .. | Excel Discussion (Misc queries) | |||
Very easy question about filter!!! | Excel Discussion (Misc queries) | |||
filter question | Excel Discussion (Misc queries) | |||
Auto Filter Limit Question | Excel Discussion (Misc queries) |