Subscribe
PodcastExtract data using Advanced Filter and VBA
In this post we will learn how to use the Advanced Filter option using VBA to allow us to filter our data on a separate sheet. This has been requested by a lot of our readers and here is how we will use them.
What we need to get this done.
1. Some data that we need filtering on.
2. Define what options we need as drop down lists
3. A cup of coffee
In the sample data, I have defined 4 options to be available as drop down list; this has been done by creating a new sheet called as “Master”. I then copied the existing columns data into this sheet and used the Remove Duplicates feature to get the unique list of items that was required for the drop downs.
The named ranges were created using the INDEX function as shown below
| Named Range | Formula |
| prd | =Master!$A$2:INDEX(Master!$A:$A,COUNTA(Master!$A:$A)) |
| rgn | =Master!$B$2:INDEX(Master!$B:$B,COUNTA(Master!$B:$B)) |
| cust | =Master!$C$2:INDEX(Master!$C:$C,COUNTA(Master!$C:$C)) |
| agnt | =Master!$D$2:INDEX(Master!$D:$D,COUNTA(Master!$D:$D)) |
Now we need to setup the sheet where we need the filtered data to be displayed. Headings were put in cells B5 to B8 and the drop down (using the Data Validation—List) feature was put in cells C5 to C8. Now we need to create or criteria fields in the RawData sheet, this is a requirement and cannot be any place else. When you use the Advanced Filter dialog box and try to place the output onto another sheet Excel will display a message saying “You can only copy data to the Active Sheet”. We will overcome this limit by using VBA and telling Excel where to put the filtered data. I used the cells M1 to P1 to define the headings and cells M2 to P2 to get the actual options from the “Filter Sheet”
| Cells | Formula |
| M2 | =Filter!C5 |
| N2 | =Filter!C6 |
| O2 | =Filter!C7 |
| P2 | =Filter!C8 |
Macro to run advanced filter and extract data
Sub FilterData()
Sheets("Filter").Select
Range("B10").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear
Sheets(“RawData”).Range(“Table1[#All]”).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets(“RawData”).Range(“M1:P2″), CopyToRange:=Sheets(“Filter”).Range(“B10″), Unique:=True
Columns.AutoFit
Range(“B10″).Select
End Sub
First we ensure the current filtered data (in any) is cleared out before we run the code again and then we get the new filtered data from cell B10 onwards. Now let’s understand the actual code that filters our data here.
Sheets("RawData").Range("Table1[#All]").AdvancedFilter
Action:=xlFilterCopy,
CriteriaRange:=Sheets("RawData").Range("M1:P2"),
CopyToRange:=Sheets("Filter").Range("B10"),
Unique:=True
We converted our raw data into an excel table (Structured Reference Structured Reference), by doing this we no longer need to know how many rows our data actually goes down to, the “Table1[#All]” takes care of that for us.
We also need to specify that our data is in another sheet and we are trying to run Advanced Filter on that data range, this is done using the first line ” Sheets(“RawData”).Range(“Table1[#All]”).AdvancedFilter “.
Next we specify the action that we need which is Copy in our case, the other option is “xlFilterInPlace” which would filter right on our data itself.
Then we have specified the Criteria Range (which needs to be on the same sheet where the data is).
And finally we have specified where the output has to be sent to by using : “CopyToRange:=Sheets(“Filter”).Range(“B10″)”
We have also made sure that only Unique records are returned to us by turning Unique:=True.
Download Advanced Filter Demo File
Click here to download the demo file & use it to understand this technique.
Do you use Advanced filters to extract sub-sets of data?
Advanced filters are very powerful and very simple to setup. I use them often to quickly extract what I want.
What about you? Do you use them often? Please share your experiences, techniques & ideas using comments.
Learn more about extracting / consolidating data using VBA
Data extraction and consolidation are one of the most common activities done by reporting professionals & analysts. No wonder we speak about these areas a lot here too. Please check out these pages to learn more:
- Split an excel file in to many using Advanced Filters & VBA [with video]
- Move data from one sheet to many using VBA
- Split text on new line using VBA
- Consolidate data from multiple files in to one using VBA Macros
Want to lean more? Consider joining our VBA Classes
If you would like to learn more about VBA programming, Excel automation, creation of user forms, manipulating data in Access thru Excel etc., consider joining our online VBA Classes. This step-by-step program helps you become awesome in VBA.
Click here to know more & Join our classes.
Sign-up for our FREE Excel tips newsletter:
Here is a smart way to become awesome in Excel. Just signup for my FREE Excel tips newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:
Your email address is safe with us. Our policies
| ||||
|
Leave a Reply
 |
Excel Links – Lets meet in Chennai this Sunday edition | Macros for Automatically Implementing Modeling Best Practices |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
81 Responses to “Extract data using Advanced Filter and VBA”
one question though, I I were to use this kind of filtering for a report, would it not be better to remove the original formatting of the data table and use target formatting instead? I am sure it can be done but I do not know how, so you can consider this a question to be answered… ;o]
In the VBA code just add some code to format to how you want your new formatting to be.
For some examples look here:
http://msdn.microsoft.com/en-us/library/office/aa224873(v=office.11).aspx
http://msdn.microsoft.com/en-us/library/office/aa173167(v=office.11).aspx
hi
the above advanced filter is great, but if i want one criteria to list all instead of particular data what should i do.. let me say i want all details of product sales how do i filter it , rest of criteria should be all no agent id name or no region or no customer type
@Sriram
To select all records matching a field Simply leave that field blank, ie: Unfiltered
I tried by adding a filed blank by extending the range of the data filter (name range) not working.. any idea pls
it workign when i use this formula “=IF(Filter!C8,””,””)” in raw data sheet instead of “=Filter!C8″, because the value is taken as 0 if we do not use if condition
i have another doubt how do we give criteria in a specified range in a separate sheet, not like the above giving in the same range… is it possible
@Sriram
Ask the question in the Forums and attach a sample worksheet
http://chandoo.org/forum/
I’ve actually created a whole add-in surrounded the advanced filter. I basically made it so it is really easy to filter data with a “text wrapper” form. My add-in is more for general purpose. I like how you did it here – if you are doing the same thing every time. Pretty clean.
This is great. I’ve been looking for a way to do this for a while. One question, what if you don’t want to as specific as the filter goes meaning maybe you want to see all the calls for Agent Bond?
“Now we need to create our criteria fields in the RawData sheet, this is a requirement and cannot be any place else”
This is not a requirement: I ran your Advanced Filter with a copy of your criteria in Filter sheet and it worked perfectly (no error messages and produced the same number of records of your macro filter).
“When you use the Advanced Filter dialog box and try to place the output onto another sheet Excel will display a message saying “You can only copy data to the Active Sheet”. We will overcome this limit by using VBA”
It’s not strictly necessary to use VBA: you can overcome this message by executing the Advanced Filter command from the sheet where you are going to put the filtered records (in other words, activate the output sheet before you run the command from Excel)
Macro Code:
Range(“B10″).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear
You are clearing the output range before the execution of Advanced Filter command: this is not necessary because this commands clears the output area of the last execution before it generates the new output of filtered records.
I hope this helps.
Hi Friends,
I am unable to practice this nice trick though i followed all procedure. can anyone help me please.I some one tell me his email id, i’ll send my file for correction.please someone help me
Hi – Is there a way to add an “All” to the drop-downs to show every “Product”, but still be able to see specific “Regions”, “Cust Type”, etc.?
Yes, try this:
For blank data, use “=” (no quotation marks);
For Non-blank data, use “<>” (no quotation marks);
For all data, use blank (nothing inside the criteria cell);
Kdu.
Hi Kdu – Not sure what you mean and where you would use the =, <>, and blank…
I meant that you should add these characters inside the cells of the filter.
And when you want all the results for a specific criteria, you kept it without data.
Got it?
If you want all products, write nothing inside the criteria cell for product and then write your other criteria for regions etc.
I have done a simple adjustment to the lists that are the source for the drop downs. I have simply added an asterisk to the beginning of the list. Since ‘*’ replaces any character it works like a charm
Hi Chandoo, once more a very useful article.
My workmate and I commonly use this feature to filter data by several criteria but I’d like to make a suggestion on the VBA code so it would work on Excel 2003 as well.
Once excel 2003 doesn’t have Table support, you should change this lines of code:
Range(“B10?).SelectRange(Selection, Selection.End(xlToRight)).SelectRange(Selection, Selection.End(xlDown)).SelectSelection.Clear
by these ones:
Dim tbl as Range
Set tbl = Range(“B10?).CurrentRegion
tbl.Select ‘You can delete this line, it will just show you what each step doesSet tbl = tbl.Offset(1, 0).Resize(tbl.Rows.Count – 1, tbl.Columns.Count)
tbl.Select ‘Same as before
tbl.Clear
You’ll need also change this line:
Sheets(“RawData”).Range(“Table1[#All]”).AdvancedFilter Action:=xlFilterCopy,CriteriaRange:=Sheets(“RawData”).Range(“M1:P2″), CopyToRange:=Sheets(“Filter”).Range(“B10″), Unique:=True
By a different reference to the table where your raw data is, using for example the top left-most cell in the range:
Sheets(“RawData”).Range(“A1″).AdvancedFilter Action:=xlFilterCopy,CriteriaRange:=Sheets(“RawData”).Range(“M1:P2″), CopyToRange:=Sheets(“Filter”).Range(“B10″), Unique:=True
So, that’s it! Now it works on Excel 2003!
Kdu.
Sorry, there is a typing error.
Please replace Range(“B10?) by Range(“B10″).
Kdu.
Or just
Sheets(“Filter”).Range(“B10″).CurrentRegion.Clear
This will also clear the headers line. Thats why I used the .Resize function.
Kdu.
I have built custom function for this as I use it all the time and want to be able to pass all the arguments to the advanced filter. Code below. (note is uses a central error handler not associated with the code)
Public Function DataFilter(rngData As Excel.Range, rngDest As Excel.Range, Optional rngCriteria As Excel.Range, _
Optional blnUnique As Boolean) As Boolean
‘ Set Routine namE
msProc = “Scorecard”
gsDesription = “Commencing Scorecard Build…”
On Error GoTo ExitProc
If blnUnique Then
rngData.AdvancedFilter Action:=xlFilterCopy, criteriarange:=””, CopyToRange:=rngDest, Unique:=True
Else
rngData.AdvancedFilter Action:=xlFilterCopy, criteriarange:=””, CopyToRange:=rngDest, Unique:=False
End If
Exit Function
ExitProc:
success = False
Call bCentralErrorHandler(msMODULE, msProc, , False)
End Function
As I said before, it’s not necessary to clear the output range, but if you want to do it, yo don’t need to Select it; this statement is simpler and works fine:
Range(Range(“B10″), Range(“B10″).End(xlToRight).End(xlDown)).Clear
Hope this help.
Why we need elaborate coding?
To clear the output range why not use Range(“B10″).CurrentRegion.Clear instead of
Range(“B10″).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear
Mike H,
It is bad form to use the select method unless absolutely necessary. It takes longer to actually select cells/sheets/ranges than to just reference them. In this case it probably isn’t as big of a deal since it is selecting so few things, but it is still important to be consistent in your coding methods.
I would be interested in using Kdu’s method without clearing, I didn’t know you could do that – but I wanted to check it out first.
@Jon
You’ve misread, Mike H’s post
Mike H suggested exactly what you said and offered an alternative using the Current region modifier
@Hui,
Ha! Yes, I did.
I also tried Kdu’s solution of just doing the filter without clearing. It doesn’t work if the latest filter has fewer items. That’s is what I was thinking, but thought maybe there was something that I was missing, good to know that clearing is important!
Hey guys,
The code I provided will declare a variable to be set with the data range and when i use the “.offset(1,0).resize(..” statement, i’m setting the variable with just the DATA from the filtered range and after clearing it up, because without it VBA will also clear the Headers row.
So, these are the steps that your code must follow:
– Select the current filtered range;
– Clear it (without the headers row);
– Filter the Raw Data using the criteria defined on the other sheet;
I’m doing this keeping the header row because actually i didn’t t check if the advanced filter also brings the headers in the result. If so, just forget the statements where i’m removing the headers from the range to be clear.
Kdu.
Try this:
– In VBA code, comment Clear Statement
– Change CopyToRange:=Sheets(“Filter”).Range(“B10″) to
CopyToRange:=Sheets(“Filter”).Range(“B10:K10″) (note : K10)
– Execute your macro
I tried this with several values of Product and it worked fine in all cases without clearing the output range before running the Advanced Filter.
It works too if you execute the Advanced Filter from sheet ‘Filter’
HTH
@CMC,
That does it! It removes all old data. Pretty nice. I’ll have to keep that one in mind.
This in an excellent article though I have a question, what if you want selected data from the raw data table….for example if I just want values pertaining to column “Call Duration” and “Call ID” only…any suggestions on how can I do that. Most of the time while working with excel data you just need selected data instead of whole table….
@Sunil,
You may want to refer to a previous article to accomplish this.
http://chandoo.org/wp/2012/04/02/using-excel-as-your-database/
~VijaySharma
Hi Jon and CMC
Am I missing something ?
I have remmed out the clear part of the code and added k10 to code as recommended however when changing from a large extract to a small I still get formatting left on screen of the extra lines. To try and replicate – Laptop, North, SME, Agent Vinod and run extract code then keep first three same but change to Agent Mary and re run again. When doing for Agent Mary with less data have six extra blank formatted lines left on table and in addition rows 17 to 20 all formatted same background pale blue not alternate as should be. If you get the same does this mean we do need to clear prior data to ensure layout / formatting is correct?
So how is it that you can not use Advanced Filter to manually paste data to a different sheet but if you use VBA it is OK??
Per Microsoft you <em>can</em> copy to another sheet without VBA.
http://support.microsoft.com/kb/117643
<em>When you choose the Advanced Filter option from the Data menu, you can only copy filtered data to another location if the destination sheet is the active sheet.</em>
@RichW,
Correction:
Per Microsoft you can copy to another sheet without VBA.
http://support.microsoft.com/kb/117643
When you choose the Advanced Filter option from the Data menu, you can only copy filtered data to another location if the destination sheet is the active sheet.
@RichW
Unfortunately the implementation of Excel Functions is not handled consistently within Excel, The Name Manager and VBA, although I am sure you expect it is
I can show you cases where using a simple Excel Function on a worksheet works fine, But when used in the Name Manager it crashes Excel
It’s been reported to MS since Excel 2007 and the bug still exists in 2013.
Obviously there to busy implementing bling at the expense of doing something right!
</End Rant>
Chandoo, if I was a girl, I would have blown kisses on you for this. I spent my whole Monday trying to do this because I need to prepare a dashboard showing dynamic organizational hierarchy according to the audience level in company. Suppose if a regional manager views the report, he should be able to see his zonal manager performance or of a zonal manager views report, he should be able to his area manager performance. Though I was not in favour to use VBA in my dashboard, but this is going to be super helpful. You made another day for me. Thanks lots. Buy yourself a lette from my side
P.S. I wish Excel introcuces some function like {=DISTINCT(ManagerNames)} in next versions. That would be far helpful on such cases. Please come up on it, if you got something.
With thanks, Ashish
Make the filtering completing dynamic without requiring the user to click the “Get Data” button by capturing the Change event on the Filter sheet. This makes the user experience a lot more responsive and engaging!
Add the following VBA to the code behind the Filter sheet where the range “filters.All” is defined as Filter!C5:C8
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range(“filters.All”)) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
FilterData
Application.ScreenUpdating = True
End Sub
Sorry but i don’t understand this instruction “Add the following VBA to the code behind the Filter sheet where the range “filters.All” is defined as Filter!C5:C8″
This is a worksheet level code. Right Click in Filter sheet tab, choose See Code and copy all the statements from Private Sub Worksheet_Change ….
HTH
I was scrolling through data in “RawData” sheet. I noticed that when the table was scrolled down, Column titles automatically turned from A to “Call ID”, B to “Date Time”… etc (works like freeze panes but only for the table)
Can you please tell me how to do it?
Thank you,
Senthil
It’s because RawData is not a (simple) range but it has been defined as a Table (this is a new concept since Excel 2007).
Google for “Tables in Excel 2007″ to learn more about Tables.
HTH,
CMC
Hi Friends,
I am unable to practice this nice trick though i followed all procedure. can anyone help me please.
I some one tell me his email id, i’ll send my file for correction.
please someone help me
Hi Abbasi ,
You can mail me your file at narayank1026(at)gmail(dot)com
Narayan
Thanks Narayan,
I have just sent you an email with attachment.
Regards
I want to do the same thing as in the article except for using checkboxes instead of dropdown menus. Doing so more than a single criteria can be chosen e.g. the one can tick Desktop and Laptop.
[…] Extract data using Advanced Filter and VBA […]
Hi,
I am using advanced filter in my code, but the problem is that when the data is copied, it copies just values, and I need it to copy hyperlinks as well. Is there a way to copy links as well?
Please see my code for reference below.
Thank you in advance,
Ida
Range(“rangeA”).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
“B8:R9″), CopyToRange:=Range(“B10:R1000″), Unique:=False
this filter is simply, great…this tool i use everyday in my macros….
Hi, thanks for this very useful filter… I have a little question… I’m trying to filter SAP transaction and it is common to have fv50 and fv50n, if I try to filter fv50 but the filter brings the info of both transactions. Can I specific that only bring exact values?
Hi Chandoo,
I have studied your artcle about Advanced Filter. Your have explained very understandble way. You have a wonderful teaching capacity.
Hi,
Few things for clarifications please since I’m new with macros and I could not let this one work.
1) Where do I put the Named range Formulas?
2) How does the Get Data button/icon get created?
3) What’s the final macro script to get it to work? Can’t follow the thread for revisions.
Thanks!
Hi,
Is there a way to limit the results to a certain number of rows so it will not update the cells below the desired output area?
For example, I’m not expecting more than 20 items for every query so I might limit the result to that number.
I hope there’s a way. :-/
Thanks!
I would also like to know please
Hi chadoo,
I have been doing this job before also. But i have a small requirement. After filtering the data according the given criteria, i need a total column in the last cell.
for example, in your table shown above, after filtering the data how to get total of the “Call-duration” and “Up-sell” fields. Further, if you give serial No., then how to get serial numbers for the filtered data.
Your comments in this regard would be very helpful for me.
Regards,
Santosh
This tutorial is great. Quick question though, I have two filters location and data type. Right now I have 30ish columns of data and it is organized by SKU with marketing information, basic, pricing and dimensions (these are my data types). Depending on the data type there are several blank columns. Is there anyway to have those columns not show up? Like when I choose basic I only need columns B-F can I get rid of G through AH?
I have the same question as aaron, how can I copy only certain columns instead of all the table?
[…] large data sources than formulas, so use them wherever possible. See Chandoo’s post Extract data using Advanced Filter and VBA and Daniel Ferry’s great post Excel Partial Match Database Lookup for examples on the […]
[…] Hi Jemma Topics like this have been covered before. Various websites list the possibilities, including an Advanced Filter: Extract data using Advanced Filter and VBA | Chandoo.org – Learn Microsoft Excel Online […]
[…] http://chandoo.org/wp/2012/11/27/extract-subset-of-data/ […]
Hi Chandoo,
Great website, it’s number one on my favorites! My question is I used this advance filter and made some tweaks by using a text box instead of drop downs for user input. Works like a charm. What I need to do though is only return certain certain columns not all of them. Example key fields are: CustID, CustName, CustAddress, CustCity, CustState, CustZip. I want all except the “CustID” to copytorange, but keep in mind user is using “CustID” to query. Any help would be greatly appreciated!
PS. I thought of using formatting text to white, but was hoping for better solution.
yes i am looking at doing the same thing
help please
Hi Chandoo, Happy New Year to you & your family!!!
Need your help here. I have a similar data but i want to filter between two dates. The date column is in the format “dd-mmm-yy h:mm AM/PM”
You can use 2 conditions. See here for advanced filter help.
http://chandoo.org/wp/2011/10/10/how-to-use-advanced-filters/
Hello using this workbook as an example, is there a code that can be applied to a separate button labelled ‘See all’ so that the table displays all the raw data from sheet 1, in to sheet 2. Its just to make it a bit more user friendly for my colleagues so they can look at one sheet with all the information on it.
Thank you
Could you please elaborate
“The named ranges were created using the INDEX function as shown below”
Novice here!!!
Me too!
What do you mean by:
“The named ranges were created using the INDEX function as shown below”
greetings!!!
Hi, i am very new to VBA and am wanting to use this for a simple report i have. I followed all the steps however i keep getting the following error message: Runtime Error ‘1004’ Application defined or object defined error.
when i debug it highlights in yellow the following piece of code:
Sheets(“raw data”).Range(“Table1[#All]”).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets(“raw data”).Range(“M1:O2″), CopyToRange:=Sheets(“Filter”).Range(“A12″), Unique:=True
Can you please help me identify what i have done wrong. I am very new to VBA and unsure of how to troubleshoot errors.
Thanks heaps for your help in advance.
i just found why this wasnt working. my table name didnt match. sorry to waste your time.
Thanks again love your work
hi,
i have done this successfully but i am now wondering how to implement a data range for this dataset. so for example: my data set has the following filters applied successfully: category and business unit. because my table of data is so extensively long i wish to add another level which they can filter records my date. However, i only have 1 date field in my table Date Raised. Essentially what i would like is a searchable date where they can filter items between two different dates Date raised or even just the one where you can say data raised >= 01/01/2014?
Thanks
jenn
in addition to this i would also like to know how i can transfer a only parts of a table rather than the entire table as mentioned in your code, i have tried specifing column ranges but it didnt seem to work.
Sorry i am still new to VBA.
Thanks heaps
Hi All.Thanks, Chandoo, for this awesome post. I have been searching high and low for a better way to parse through a lot of data, and this answers it. I do have two questions, though.
1. When I execute the macro, the column widths on the destination page autofit to the data. Is there some way to prevent this?
2. After I save the workbook as a macro-enabled workbook, I can no longer use the table features on my original raw data (the table button is greyed out). This is a huge bummer? Anyone run into a fix for this?
Thanks!
Monica
@Monica
Q1. Comment out the line by adding a single quote
‘Columns.AutoFit
Q2. Sounds odd, I haven’t encountered that issue ?
Ha! I didn’t even notice the autofit line in the code! Thanks. Sometimes the answers to what seems like a very difficult challenge are very simple.
I’m going to try to see if I can replicate issue number 2 in a new spreadsheet. Very strange!
Thanks @Hui!
Hi, Thanks for this info!! Quick question, one of my filter parameters literally has about 10000 entries in it. It is quite tedicious to scroll 1 by 1. Is it possible to have a search within that filter so I won’t have to scroll all the way through the filtered. (ie; if the first 2 letters are of the word “first”, I would just like to enter in “fi” and then all the results with those first 2 letters should scroll to that portion automatically.
If I want to filter using more than four categories, how do I do this?I’ve tried to add them using the demo file, but with no success.
I have a problem while using advanced filter.
Based on the user’s resonse,filter the table in the Activities sheet with an Advanced filter,to display only the activities matching the specified category.If the user doesn’t enter one of the specified categories,then your macro must clear the filter(and the cell containing the filter criteria),so that all activities are displayed.Use a conditional Statement to achieve this.
how to create a total call duration and total up-sell at the last row in the same table after i press get data button thnx for answer
after Columns.AutoFit add this code
lastRow = ThisWorkbook.Sheets(“Filter”).Cells(Rows.Count, 7).End(xlUp).Row
ThisWorkbook.Sheets(“Filter”).Range(“g” & lastRow + 1) = _
Application.WorksheetFunction.Sum(ThisWorkbook.Sheets(“Filter”).Range(“g11:g” & lastRow))
ThisWorkbook.Sheets(“Filter”).Range(“g” & lastRow + 1).Font.Bold = True
I’m having issues adding additional selection variables……how do I had a search box to go along with the 4 dropbox variables provided, where it’s blank and I can simply enter a value, such as “>100″?