|
|
|
|
|
|
Excel
|
Two Validation List with automatic updating of first data of second validation list
|
|
Ease of Use
|
Easy
|
Version tested with
|
2002
|
Submitted by:
|
chitosunday
|
Description:
|
Two validation list (Major Group/sub group) that automatically update the sub group putting the first data of the sub group in the second validation cell
|
Discussion:
|
Most of us use the validation list with the indirect function on the second validation list. The disadvantage of using indirect function is that it is not automatically seem to be adjusted unless you click the second validation list. This method automatically update the second validation list inputting the first data of the chosen range.
|
Code:
|
instructions for use
|
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("e4"), Target) Is Nothing Then
Dim xx As String, zz As String
xx = Range("e4").Value
zz = Range(xx).Address
Range("g4").Value = Range(zz).Cells(1).Value
Range("g4").Validation _
.Modify Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & zz
End If
End Sub
|
How to use:
|
- Put the above code in the specific sheet code .
-
- The intersect command will limit the firing of the macro to changes made in cell e4.
-
- xx is the container for the value of range e4
-
- zz is like a indirect function that converts the xx to it's reference range
-
- Range("g4").Value = Range(zz).Cells(1).Value just input the first data of ref range
-
- The last statement "validation" update the range of the validation list.
-
- In the sheet, you must name the range the sub service group into the name of the major reference group by using insert, name ,define or highlighting the range of the sub-group then clicking the name box (left upper of the menu) then typing the major ref name
|
Test the code:
|
- The source data (complete list) of the major group is in column A while the sub group is in column c.
-
- Choose the major reference in cell "E4" . In Cell "G4", the first data of the sub service list will appear.
|
Sample File:
|
validationlist.zip 12.77KB
|
Approved by mdmackillop
|
This entry has been viewed 325 times.
|
|