Have you ever wanted to manage a cascading data validation list without the need for dozens to hundreds of named ranges? Well this may be the tutorial for you. This method creates data validation lists on the fly and allows you to manage a vertical list and add new columns to the list and have the data validation list update seamlessly.
The following is a short video explaining the procedure in a lot more detail. Seeing is believing.
Let’s have a look at how the data is laid out inside the spreadsheet. Contrary to the traditional method the data is laid out in rows and the 3 lists in the below example are managed by row vertically. In my opinion this is far easier to manage as you don’t have to add a couple of named ranges every time a new department is added. I will add the file at the end of the article so you can see the full list of items.
The general premise is to take this list and give users option depending on what Department was chosen and restrict the category to the singular department that is chosen. So if Auto was chosen then only Cleaning and Accessories would appear in the second data validation list. Then If Cleaning was chosen then Engine Wash, Oil Clean, Windows & Pumpit would appear in the third Combo box.
Here is the example of what we might like to see above. The data is cascading down beautifully. Now if we were to add a new auto category. Then the data would update inside the data validation lists. Why would this happen? Because the data is being updated in real time, on the fly as we say.
How is this happening?
There is a VBA procedure that sits behind the process.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [E4:G27]) Is Nothing Then
AddtoList
End If
End SubThe process above says that the procedure will operate in the cells E4 to G27. If you have different cells then this range needs to change. I have restricted it to a small size for this demonstration. The procedure that makes the data validation update is called AddtoList.
Add to List
The following is the procedure I will give a brief overview below of what is happening.
Sub AddtoList()
Dim n As Integer
Dim i As Integer
Dim ar As Variant
Dim str As String
Dim z As Integer
Dim r As Range
Dim txt As String
Dim j As Integer
ar = [A1].CurrentRegion 'Send to the Array
ActiveCell.Offset(, 1).Activate ' move one column
Set r = ActiveCell 'Set range
j = Cells(2, ActiveCell.Column).Value '1 or 2
z = j + 1 'Choose between 2 or 3rd column
On Error GoTo Trapper:
For i = 1 To UBound(ar) 'Length of the list
For n = 1 To j 'Column Loop
If ar(i, n) <> r.Offset(, -(z - n)).Text Then Exit For
Next n
If n = j + 1 And InStr(str & ",", "," & ar(i, n) & ",") = 0 Then
str = str & "," & ar(i, n)
End If
Next i
txt = Mid(str, 2)
r.Validation.Delete
r.Validation.Add xlValidateList, xlValidAlertStop, ,txt
Trapper: Exit Sub
End SubThe procedure is effectively sending the data list in its entirety to an in memory array called ar. This array stores the list and we can then check the validity of the list with some looping constructs. In F2, G2 and H2 I have put the numbers 2, 3, 4. This is to enable the code to go back and check the prior column. The first part of the looping construct checks what is in the array against what was chosen by the Op in the cell.
If ar(i, n) <> r.Offset(, -(z - n)).Text Then Exit For
If they do not match then the next check is evoked:
If n = j + 1 And InStr(str & ",", "," & ar(i, n) & ",") = 0
The above checks for a valid string. If the string is valid the str short for string is updated with a comma to separate each element of the string.
str = str & "," & ar(i, n)
What it is trying to replicate is the string inside the data validation list which would appear like this auto, clothing, garden. If that can be achieved in a string then it can be sent to the data validation list and will be added with the following lines.
A txt variable is declared and the variable uses the mid function to add everything from the second character as the first character is always a comma in the string. So it excludes this leading comma ,auto, clothing, garden
txt = Mid(str, 2)
The above line where 2 is show says …… start from character 2.
Next the data validation in the list is cleared and the new string called txt is added as the new data validation list.
r.Validation.Delete
r.Validation.Add xlValidateList, xlValidAlertStop, , txt
The final line above just sets the new validation list to be equal to txt. It is really neat and happens very quickly as it is done in memory after the data is stored in the array.
As I mentioned at the start it is super efficient and avoids up to hundreds of named ranges depending on the complexity and size of your data table. It might just be the solution for you.
I was designing a solution for a client of mine and decided this was more of a global breakthrough and thought I would share it here, on YouTube and on Linkedin. The response has been fairly muted but I am used to that. I don’t believe there is a solution like it online and it may well be the one for you.
Here is the workbook that covers this tutorial.
