Formula to copy text when column is sorted

[edrop]

LABELING PROJECT: I am using Microsoft Excel 2007 and have a column containing different last names. When I filter this column to a particular last name (i.e. "Smith"), I need a cell that is located outside of this column to show this last name. What formula can I use on this cell so that when I filter to a different last name (i.e. "Brown"), the cell value will automatically change to indicate this newly selected last name?

 

[jskushawah]

Welcome to the forum


if filtered cell is B2 and you want to this value in F2 then apply =B2 in F2


regards!

 

[edrop]

Thanks for responding jskushawah! But if I filter column B to a different last name, I need F2 to automatically reflect this newly selected last name. Any ideas which formula to use?

 

[Debraj]

Hi edrop,

What if, I will create a Formula for you..


Open VBA (Alt + F11)

Insert a New Module

Paste the below Code.

[pre]

Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As String
Dim strCri2 As String
Application.Volatile
With Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)
If Not .On Then Exit Function
strCri1 = .Criteria1
If .Operator = xlAnd Then
strCri2 = " AND " & .Criteria2
ElseIf .Operator = xlOr Then
strCri2 = " OR " & .Criteria2
End If
End With
End With
AutoFilter_Criteria = strCri1 & strCri2
End Function[/pre]

Now in F1 enter Formula as

=AutoFilter_Criteria("B1")

For details refer to

http://chandoo.org/forums/topic/autofilter-criteria-operator#post-21273

or download https://dl.dropbox.com/u/78831150/Excel/AutoFilterCriteria.xlsm


Regards,

Deb

 

[edrop]

Debraj Roy! You are a GENIUS! But not just a GENIUS, you are a very thoughtful and generous GENIUS!!! Thank you for sharing your knowledge with me and others seeking answers! This was exactly what I was looking for, EXACTLY! and IT WORKED PERFECTLY! THANK YOU, THANK YOU, THANK YOU from the bottom of my heart! Thanks to Chandoo for connecting me with you!!!

 

[mercatog]

HiAnother way by formula


In G1 ( refering to file added by Debraj Roy) write this formula. It retrieves the first visible value in column C(after filter or not)

{=INDIRECT("C"&MATCH(1,SUBTOTAL(3,OFFSET(C2:C100,ROW(C2:C100)-MIN(ROW(C2:C100)),,1)),0)+1)}

 

[Debraj]

Hi Mercatog,


Nice Formula, But it's useless....

...

unless you inform to enter the formula with Ctrl + Shift + Enter

Regards,

Deb

 

[mercatog]

Yes Roy, Thanks God you're here to show me the way

Of course, I forgot to mention that formula is an array forumla that should be validate by Ctrl Shift Enter(after translating formula in English).


I've edited my post and correct it.


Thanks Roy