Autofilter - Return a Value
 

Hi,

I have a database list that I apply the AutoFilter to, eg "Dec" to
displat December's data.

Even though it's obvious what month is displayed, is there any way to
have a cell display the value of the filter, in this case "Dec"
displayed in a cell?

Regards,
Kay S

Tks Ken for the 2 responces ... but I have col A entries of Jan, Feb,
Mar etc entered as straight text rather than dates.

Users will be filtering on that column only also. This data is
imported as text unfortunately.

So, any ideas on how the filtered month can displayed in a cell
returned if it is entered as text in col A?

Regards, Kay

On Sun, 31 Oct 2004 11:45:38 -0000, "Ken Wright"
wrote:

The other option is to use Data / Filter / Advanced Filter and actually use the
cell you want to create as the Filter criteria.

Hi,

I have a database list that I apply the AutoFilter to, eg "Dec" to
displat December's data.

Even though it's obvious what month is displayed, is there any way to
have a cell display the value of the filter, in this case "Dec"
displayed in a cell?

Regards,
Kay S

Are they real dates? if so then just use a SUBTOTAL formula to return the MAX
value of the filtered list and format it the same as the dates.

=SUBTOTAL(4,A1:A1000)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Kay" wrote in message
...
Hi,

I have a database list that I apply the AutoFilter to, eg "Dec" to
displat December's data.

Even though it's obvious what month is displayed, is there any way to
have a cell display the value of the filter, in this case "Dec"
displayed in a cell?

Regards,
Kay S


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 29/10/2004

The other option is to use Data / Filter / Advanced Filter and actually use the
cell you want to create as the Filter criteria.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Kay" wrote in message
...
Hi,

I have a database list that I apply the AutoFilter to, eg "Dec" to
displat December's data.

Even though it's obvious what month is displayed, is there any way to
have a cell display the value of the filter, in this case "Dec"
displayed in a cell?

Regards,
Kay S


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 29/10/2004

In that case...

=INDEX(A3:A30,MATCH(1,SUBTOTAL(3,OFFSET(A3:A30,ROW (A3:A30)-MIN(ROW(A3:A30)),,1)),0))

which must be confirmed with control+shift+enter instead of just with
enter.

Adjust the range to suit.

Kay Wrote:
Tks Ken for the 2 responces ... but I have col A entries of Jan, Feb,
Mar etc entered as straight text rather than dates.

Users will be filtering on that column only also. This data is
imported as text unfortunately.

So, any ideas on how the filtered month can displayed in a cell
returned if it is entered as text in col A?

Regards, Kay

On Sun, 31 Oct 2004 11:45:38 -0000, "Ken Wright"
wrote:

The other option is to use Data / Filter / Advanced Filter and
actually use the
cell you want to create as the Filter criteria.

Hi,

I have a database list that I apply the AutoFilter to, eg "Dec" to
displat December's data.

Even though it's obvious what month is displayed, is there any way
to
have a cell display the value of the filter, in this case "Dec"
displayed in a cell?

Regards,
Kay S


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273955

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.

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
'==============================================


Kay wrote:
Hi,

I have a database list that I apply the AutoFilter to, eg "Dec" to
displat December's data.

Even though it's obvious what month is displayed, is there any way to
have a cell display the value of the filter, in this case "Dec"
displayed in a cell?

Regards,
Kay S


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

LOL - Just in case I hadn't mentioned it for a while, "Aladin - You da man!!!"
:-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Aladin Akyurek" wrote in message
...

In that case...

=INDEX(A3:A30,MATCH(1,SUBTOTAL(3,OFFSET(A3:A30,ROW (A3:A30)-MIN(ROW(A3:A30)),,1)),0))

which must be confirmed with control+shift+enter instead of just with
enter.

Adjust the range to suit.

Kay Wrote:
Tks Ken for the 2 responces ... but I have col A entries of Jan, Feb,
Mar etc entered as straight text rather than dates.

Users will be filtering on that column only also. This data is
imported as text unfortunately.

So, any ideas on how the filtered month can displayed in a cell
returned if it is entered as text in col A?

Regards, Kay

On Sun, 31 Oct 2004 11:45:38 -0000, "Ken Wright"
wrote:

The other option is to use Data / Filter / Advanced Filter and
actually use the
cell you want to create as the Filter criteria.

Hi,

I have a database list that I apply the AutoFilter to, eg "Dec" to
displat December's data.

Even though it's obvious what month is displayed, is there any way
to
have a cell display the value of the filter, in this case "Dec"
displayed in a cell?

Regards,
Kay S


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273955



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 29/10/2004

Thanks so much Ken, Aladin and Debra!

Regards, Kay


On Sun, 31 Oct 2004 08:50:32 -0500, Debra Dalgleish
wrote:

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.

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
'==============================================


Kay wrote:
Hi,

I have a database list that I apply the AutoFilter to, eg "Dec" to
displat December's data.

Even though it's obvious what month is displayed, is there any way to
have a cell display the value of the filter, in this case "Dec"
displayed in a cell?

Regards,
Kay S