Often you might want to validate cell content in Microsoft Excel with a list of values. Say, only this content should the cell accept. E.g.: A cell should only accept the following names ôSam, Cathy, and Tomö. Most of us would know that, Data Validation is option to use.
For those who are not aware, here is an overview of Validation option available in the Data menu of Microsoft Excel. You can find few examples @ Microsoft here.
LetÆs go one step ahead, to see how to do this dynamically. I have taken a case with following lists:
Figure 1
I am assuming the following data sheet:
Figure 2
Now, we want the sub-type column to be filled in depending on Type selected and Price depending on the sub-type selected.
Been with such a task before? LetÆs see how dynamic data validation can come in handy.
Steps:
1. Having your list on a separate sheet can be more convenient if it needs to be updated at a later stage. I have created a separate sheet called Criteria and added my lists to it. Refer Figure 1 above.
2. Now, your list range should be named. For e.g.: For the list, Type, select A2:A3 ignoring the column heading, from Insert menu, choose Name -> Define. Use the column heading ôTypeö as the name and click Ok. Similarly name other list ranges too using their column heading as the name.
3. Now, move to the sheet and select the cell where you want to use this list. In our case, B2 of ôDataö Sheet. Then from, Data menu, select Validation.
4. In the validation Criteria, select List, and in the Source box, type ô=typeö without quotes.
5. Click Ok. This would fetch all values from Type range in Sheet ôCriteriaö and fill them up in a drop down box at B2 cell ôDataö sheet.
6. Now, we want the column C to list down the values depending upon the value select in column B2. For this, select the C2, from Data menu, select Validation. In the validation Criteria, select List, and in the Source box, type ô=indirect($B$2)ö without quotes. Click ok and ignore any error messages displayed.
Figure 3
Now, what does Indirect($B$2) do ?
The function Indirect, would parse any text provided and use that as reference. Here, in our case, the text select in cell B2 would be used as a reference in cell C2.
If you want set validation for multiple cells, select cells in column C, with the first cell has the focus. If I have selected the range C2:C10, I will make sure the cell C2, has the focus. Then access, Data > Validation and enter the Source as ô=indirect($B2)ö. This would automatically change the following cell reference to the cell adjacent to it.
7. Follow Step 6 to refer column C likewise to fill in price info.
8. Test the validation by selecting the values.
Figure 4
Using VBA
You can also fill in validation list dynamically using VBA. Every sheet would have a SelectChange event within which your code to dynamically validate should work.
Here is the dynamic validation code sample that would fill in values:
Cells(activecell.row,2).select æ Assuming the 1 is where we have our content
Myval = <your list generated at runtime>
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=myval
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = “”
.ErrorTitle = “”
.InputMessage = “”
.ErrorMessage = “”
.ShowInput = True
.ShowError = True
End With
Download a sample excel file from here
Cheers
