Display Name of customer from FILTERED DATA.

  • Thread starter Thread starter S. Obaid
  • Start date Start date
S

S. Obaid

Hi,

Is there anyway to GET data (Customer Name..currently filtered)
from one of the column of FILTERED DATA.Suppose I filtered
Data on Customer "A". I want to capture the Name of This
Customer "A" and display this name on another cell(CellA1). If filtered
data is changed and record is Filtered to Customer "B", cell A1
display "Customer B".

Hope this time I made it clear
Thanx in advance for your help

Syed
 
Syed,

If you want a live link to the filtered data, I think not. Advanced filter,
with the "Copy to another location" is generally used for that. But it's a
process that must be performed, not a live link.
 
To show the value that has been selected in the AutoFilter dropdown, you
can create a User Defined Function. Tom Ogilvy posted the following
function, that returns the criteria from a column in an autofiltered
table. It will show both criteria if there are two, and includes the
operator.

David McRitchie has instructions for storing a macro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro

On the worksheet, create a formula that refers to the cell that contains
the Customer heading. For example, in cell H1, enter:
=ShowFilter(A1)&CHAR(SUBTOTAL(9,A2)*0+32)

'===============================================
Public Function ShowFilter(rng As Range)
'UDF that displays the filter criteria.
'posted by Tom Ogilvy 1/17/02
'To make it respond to a filter change, tie it to the subtotal command.
'=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32)
'So the above would show the criteria for column B

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
'==============================================
 
sorry.... I couldn't understand .....
I typed the same formula in cell after copying Showfilter Function
in VB editor. But when I change the filter nothing happen...
its displaying #Name error...Further I typed a msgbox line
in that VB code to check if the formula is being initialized or not
but I didn't get any msg....!!!!!!!

Plz comment

Syed Obaid
 
Back
Top