Using an IF statement for a filter [SOLVED]

[jassybun]

Hey guys! How has everyone been?


I am using a filter, but I want to show at the top (above filter) what I am filtering.

I made these functions, but unfortunately they include the hidden rows, which obviously doesn't help. How can I only show what I am filtering? The filter being used is the P column.


Cell A1=IF(ISNA(MATCH(6001,P8500,0)),"","6001")

Cell A2=IF(ISNA(MATCH(6002,P818,0)),"","6002")

Cell A3==IF(ISNA(MATCH(6006,P818,0)),"","6006")

 

[Luke M]

If only one of those numbers will be shown, you could do:

=IF(SUBTOTAL(103,P8500)=ROWS(P5500),"",SUBTOTAL(101,P8500))

What's it doing? We first check to see if the number of visible cells equals total number of cells in range. If it does, there's no filter, so display "". If we do have a filter, and agian, making BIG ASSUMPTION that only 1 number is chosen, we display the average of visible cells. Since it's only 1 visible number, the average = that number.

 

[jassybun]

I would want to show whichever ones are being filtered..like a distinct value.


None filtered would show this:


6001

6002

6006


(filter)Area.....Amount

........6001.....4

........6001.....10

........6002.....1

........6006.....10


Should you filter only 6001, it would look like this:


6001


(filter)Area......Amount

........6001......4

........6001......10


Is that possible?

 

[NARAYANK991]

Hi ,


Try this :


=IF(SUBTOTAL(101,$P$8:$P$500)<>SUBTOTAL(104,$P$8:$P$500),"",SUBTOTAL(104,$P$8:$P$500))


This assumes that the Area item is a numeric value , not text.


Narayan

 

[Luke M]

@jassybun

Yes, I thought that's what my formula did. Was it not working like you wanted? I suppose you could change the "" to be "All" like this:

=IF(SUBTOTAL(103,P8500)=ROWS(P5500),"No filter",SUBTOTAL(101,P8500))

 

[Smallman]

JassyB


This is a tricky one for formula to handle because when you need to return multiple instances of the filtered list a formula is going to struggle. I could be wrong on that but I can't see how it can return all instances in one formula.


I can't offere an addition for anyone looking to return numbers or text. Put a volitile formula in Cell P1 say Today()


Put the following in the worksheet where you want this to fire from ie sheet1.

[pre]

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
[P2] = Range("P8:P800").SpecialCells(12).Cells(1, 1).Value
Application.EnableEvents = True
End Sub

[/pre]

It will give you a single filtered result be it numbers or text.


If you want all filtered results then the following will give you exactly what I understand your needs to be.


http://j-walk.com/ss/excel/usertips/tip044.htm


Gave it a test and it seems to go well.


Take care


Smallman


ps. Naranyan the code tip you gave me works a treat. Thanks again for the screenshot.

 

[jassybun]

Smallman - thanks for that link - I have never heard of that site but that article is exactly what I was talking about! Thank you Luke and Narayank as well. I am using the equation supplied and it is working


You guys are awesome.

 

[Luke M]

Glad we could help, thanks for letting us know jassybun.