Dynamic Filters in Excel?

Question

I'm using Office 2013, and working on a worksheet in Excel.

My question is, is there a way to create a dynamic filter in Excel?

To explain in more detail, I have a dynamic worksheet, where upon opening the user will get a few drop-down options. After the user has selected one option from each of the drop-downs, the worksheet will display a table of data based on the user's inputs. The user can change their selections from the drop-downs after the table has been displayed, and can also clear their selections. If they clear their selection, the table will disappear.

Now, the first column in the result Table will contain Text values, but can also contain blanks. These values or blanks are all decided based on the user's selections in the previously mentioned drop-downs, which are displayed permanently to the left of the table. I want to add a filter to this first column of the result table (and to the rest of the table with it) such that only the non blank cells are displayed in the table every time the drop-downs are changed

Answer 1

That can be done by using VBA to 'filter' blanks whenever the cell containing the first drop down changes. If the change results in a blank value in the cell, the event handler will 'show all'.



If you can provide more information as to the sheet name, range of cells comprising the table, and the cell reference containing the first validated cell, it would be possible to create the event handler for you.

Answer 2

Go to Sort & Filter

Select filter

Select the filter for the column in question

This provides you with three checkboxes

Select All, Prime, Blanks

Remove the Tickmark from Blanks

Only non blank records are displayed

Answer 3

MS officially calls it Auto Filter -- below are 2 links that discuss how to turn it on/use it. The first is from the 'dummies' series and then the official MS help for Excel Auto Filter. I am not calling you a dummy either -- I really like a lot of the dummies info - so I often use it and refer others to it. Hope this helps.



http://www.dummies.com/how-to/content/the-excel-2013-autofilter-feature.html



http://office.microsoft.com/en-us/excel-help/use-autofilter-to-filter-your-data-HA102809796.aspx