Howto show filtered name on separate cell on same sheet.

[Nivlem]

Greetings:

97-2003 Excel

If colum A consists of Mary,Michael and Mark and I filtered for Miachel only, how do I show the text "Michael" onto a separate cell in the same work sheet?

 

[VoG]

See if this helps http://www.mrexcel.com/forum/showthread.php?t=347226

 

[Nivlem]

Sorry I don't understand that code or how to enter in VBA?

 

[VoG]

Press ALT + F11, select Module from the Insert menu, mpaste into mthe white space on the right

Function FilterCrit(Rng As Range) As String
    Dim Filter As String
    Filter = "{All}"
    With Rng.Parent.AutoFilter
        If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
        With .Filters(Rng.Column - .Range.Column + 1)
            If Not .On Then GoTo Finish
            Filter = .Criteria1
            Select Case .Operator
                Case xlAnd
                    Filter = Filter & " AND " & .Criteria2
                Case xlOr
                    Filter = Filter & " OR " & .Criteria2
            End Select
        End With
    End With
Finish:
    FilterCrit = Filter
End Function

Press ALT + Q.

Enter the formula in the worksheet.