I was recently working on an automated procedure to generate data in a list based on drop down (data validation) selection. The idea is to change items in a drop down (data validation) and have the corresponding data table filter to the specific items chosen in your drop downs. I have shown a formula based solution for this method (a long time ago) so if a more simplistic solution hits the mark - the link can be found here.
Filter with Formula
The first thing to do is set up the validation lists. I have some vehicle crash information and have set up 3 lists. Day of week, crash type and road user. These three criteria will be used to filter the master list.
A nice to have with this procedure is the ability to filter on the items chosen in the drop down or the consolidation of the items chosen (all items as well as a single item). For example I may want to see accidents on a Sunday but also I may want to see accidents involving multiple vehicles on All Days. So the ability to show part and the whole.
Using formula can assist with this as with the assistance of the wildcard you can create the ability to filter all based on the selection.
All Days
Can become the Wildcard * character with a simple IF statement.
=IF(K2="All Days","*",K2)
Now the wildcard will appear if All Days is chosen.
Follow the same procedure for the Crash Type and the Road User and you now have the formula showing one or all for each criteria.
So onto the procedure to run the program. It will need to run if any of the cells from K2 to K4 change. So an on change event will need to be called.
Option Explicit Private Sub Worksheet_Change( ByVal Target As Range) The above will fire if any of the cells from [K2:K4] changes. Now we just have to evoke the Advanced Filter in order to make the procedure hum. here is the VBA coding for the advanced filter. Sub AdvFilt() 'Excel VBA to run the Advanced Filter.
If Not Intersect(Target, [K2:K4]) Is Nothing Then
AdvFilt 'Run when cells above change
End If
End Sub
Dim rng As Range
Set rng = Range("A9", Range("T" & Rows.Count).End(xlUp))
rng.AdvancedFilter 1, [C5:E6], 0
End Sub
The formulas for the above are held in cells [C5:E6] these give the advanced filter the criteria. The procedure works well and quickly to isolate the data you choose. Here is a file which will help to further demonstate how this wonderful procedure works.
