How to identify text from a autofiltered list using formulas

[Guest]

I have a worksheet with 8 columns of information.
I am using the AutoFilter command to display only specific information from
the list.
Is there a way, using formulas, to extract information from only the
displayed data?

Ex: One of the columns being sorted contains state names, using the
Autofilter command, data for the state of New Jersey is displayed. Is there a
way, using formulas, to extract the state name from the displayed data so it
can be used in concatenating descriptions and used as a lookup criteria in
other formulas?

Thanks

 

[Mallycat]

You could create a custom function. This will get you started but it is
not perfect

Function FilterSelection(Column As Integer)

With Worksheets("sheet1")
If .AutoFilterMode Then
With .AutoFilter.Filters(Column)
If .On Then FilterSelection = .Criteria1
End With
End If
End With
FilterSelection = Right(FilterSelection, Len(FilterSelection) - 1)

End Function

You currently have to select which column the filter is in
=filterselection(2) for the second column. Also you have to specify
the sheet name in the function (not ideal). Finally when the pivot
table changes, the function doesn't refresh, but this could be
addressed with another macro

Matt