Suggestions example file
Dependent Validation

1 Dependent validation

You can assist a user selecting items from a database using dependent validation.
In general validation in Excel is restricted to data in the same sheet, unless you use named ranges.
To keep these named ranges up to date you will have to define dynamically named ranges, but Excel lacks fullproof methods to do so.
Things get even more complicated if you prefer validationlists that contain only unique items.
Using VBA no such restrictions exist. It's relatively simple to implement dependent validation; there are even many ways to do so.
We'll illustrate the use of 2 loops; in the attached file 'method II' you will find a second method.

2 Code description

The sheet 'database' contains the data.
To the cells A2, B2 and C2 respectively in sheet 'output' an empty validationlist has been assigned.

On opening the workbook:
- assign the complete database in sheet 'database' to array sn
- produce a list containing unique items in the first column of array sn and make it the validationlist of cell A2.
- the validationlists of cells B2 and C2 will be emptied
- the contents of cells B2,C2 and B5:B7 will be cleared
Private Sub Workbook_Open()
sn = Sheets("database").Cells(1).CurrentRegion

For j = 2 To UBound(sn)
If InStr(c01 & ",", "," & sn(j, 1) & ",") = 0 Then c01 = c01 & "," & sn(j, 1)
Next

With Sheets("output")
.Range("A2").Validation.Modify 3, 1, 1, Mid(c01, 2)
.Range("B2").Validation.Modify 3, 1, 1, ","
.Range("C2").Validation.Modify 3, 1, 1, ","
.Range("B2:C2,B5:B7").ClearContents
End With
End Sub
When a cell containing a validationrule will be selected its validationlist will be produced:
- check whether the cell contains a validationrule
- check whether the cell on which this cell's validationlist is dependent isn't empty
- filter the items from the database that match the previously selected values
- produce from this filtered database a list with unique items in the corresponding column.
- assign this list to the activecell's validationlist.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$B$2", "$C$2"
If Target.Offset(, -1) = "" Then Exit Sub

sn = Sheets("database").Cells(1).CurrentRegion
For j = 2 To UBound(sn)
For jj = 1 To Target.Column - 1
If sn(j, jj) <> Cells(2, jj) Then Exit For
Next

If jj = Target.Column And InStr(c01 & ",", "," & sn(j, jj) & ",") = 0 Then c01 = c01 & "," & sn(j, jj)
Next
Target.Validation.Modify 3, 1, 1, Mid(c01, 2)
End Select
End Sub

3 Method II

In this file the same functionality is being implemented using somewhat more compact VBA-code.

Method II