Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
litLIB: added function FilterCriteria (Read 2158 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
litLIB: added function FilterCriteria
18.12.09 at 13:31:48
litLIB: added function FilterCriteria
The FilterCriteria spreadsheet function was added to the litLIB functions library. FilterCriteria displays the filter criteria that are applied when autofilter is switched on.  
This function will display the filter criteria that are applied on field B5.
Often when a large table is filtered using the auto filter Excel tool and when several critria are entered it is not possible to tell which criteria are entered. Using this formula it becomes very easy to display this.
Consider the following example. I have taken a members list of U.S. house of representatives and applied the autofilter on the list. To visualise which filters are applied I have used the =FilterCriteria function.  
Before I explain how I created this spreadsheet, let's have at it. Note that this spreadsheet is attached to this topic, so you can also have a look at it yourself.

To make your auto filtered data look like this you only need to do the following:
-Before setting the autofilter, add a blank line below the titles.  
-Enter the following formula =FilterCriteria(B5), obviously you set instead of B5 the address of the first field in that column.
-Copy the formula on the empty row for each column that makes up the table
-Now select the table, excluding the headers. So the first row is the row with the functions you just created
-Make the auto filter, et voila!
I like it when the filters are grayed a bit. Change the font color to gray. Just a matter of taste!
Back to top
« Last Edit: 18.12.09 at 13:35:46 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1