Creating Formulas from Filtered Data
I am trying to create a formula, say in cell B1 that would return the same
info that is returned from my filter. For example if i filter my data on colors and I return a 'red' result, I would like cell B2 to read 'Colors - Red' |
Creating Formulas from Filtered Data
See this:
http://tinyurl.com/2885hu -- Biff Microsoft Excel MVP "RBG" wrote in message ... I am trying to create a formula, say in cell B1 that would return the same info that is returned from my filter. For example if i filter my data on colors and I return a 'red' result, I would like cell B2 to read 'Colors - Red' |
Creating Formulas from Filtered Data
Courtesy of Tom Ogilvy
You use it like so =L1&":"&showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+32) The CHAR(SUBTOTAL bit is just to force a recalc if the criteria are changed 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 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RBG" wrote in message ... I am trying to create a formula, say in cell B1 that would return the same info that is returned from my filter. For example if i filter my data on colors and I return a 'red' result, I would like cell B2 to read 'Colors - Red' |
All times are GMT +1. The time now is 03:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com