cell link from the filtered data

[Guest]

hello there,
I have this data:
column A
xxxx
xxxx
zzzz
zzzz
zzzz
xxxx
yyyy
when I filtered it & choose the xxxx, the data shows this:
xxxx
xxxx
xxxx

Now, my problem is, i want to make a formula which can give a result from
the filtered data. In this case the result should be xxxx.

can somebody help me with this.

 

[Guest]

Here's a nice Function **written by Stephen Bullen** that has helped me
overthe years. Put in a Standard module and in your spreadsheet enter:

=FilterCriteria(AFtableRange)
HTH

Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
Dim Filter As String
Filter = ""
On Error GoTo Finish
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 " & .Criteria
Case xlOr
Filter = Filter & " OR " & .Criteria
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function

 

[Guest]

Now I got what I really need.

Many Thanks Jim.

Here's a nice Function **written by Stephen Bullen** that has helped me
overthe years. Put in a Standard module and in your spreadsheet enter:

=FilterCriteria(AFtableRange)
HTH

Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
Dim Filter As String
Filter = ""
On Error GoTo Finish
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 " & .Criteria
Case xlOr
Filter = Filter & " OR " & .Criteria
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function