Dependent Lists in Excel

R

rajeshkumar

Hi,
I've created a dependent list in Excel using Data validation. The first
list is independent and the second list depends on the first list. I'm
facing one problem in this. After selecting a element in first list and
corresponding element in second list, if I want to change the first list
again, I can go and change the value. But the value in the second list
remains the same. I want the second list to be empty when I' selecting
the first list again. How can I do this?

Thanks in advance

Regards,
Rajesh
 
R

Ron Coderre

Try this:

For my example, I'll assume :
List 1 is in Cell A1 (independent)
List 2 is in Cell C1 (dependent)

Right click on the sheet tab and select "View Code"
(That will open the VBA editor)

Copy/Paste this code into the sheet module:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("C1").ClearContents
End If
End Sub


Switch back to the worksheet and test.

Does that help?
Ro
 
R

rajeshkumar

Thanx. This is working great.
But what am I supposed to do, if I want to extend the range of the
columns?
Also when I'm doing my selection, I want the corresponding dependent
cell to get affected.

Regards,
Rajesh
 
R

Ron Coderre

If:
The independent validation list cells are in A1:A10
The dependent validation list cells are in C1:C10 then try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Target.Offset(RowOffset:=0, ColumnOffset:=2).ClearContents
End If
End Sub

Does that help?

Ron
 
R

rajeshkumar

Now I'm facing some more problem.
When I tried to insert a row in between, it is throwing an error or i
is clearing all the cells in the dependent column.

Can anyone help me out in this
 
R

Ron Coderre

Here's something to try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=2).ClearContents
End If
End Sub


However, as coded above, inserting rows won't change the A1:A10 test.
You should consider replacing that hardcoded reference with a name
range:

Insert>Name>Define
Name: rngIndependents
Refers to: $A$1:$A$10
Click [OK]

That way, when you insert (or delete ) rows the amended code (se
below) will still work as designed.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("rngIndependents")) Is Nothing Then
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=2).ClearContents
End If
End Sub


Does that help?

Ro
 
R

Ron Coderre

One more comment:

The last code I posted doesn't capture every possible scenario but it
gives you something to work with. Experiment and post back with any
quesitons.

Regards,
Ron
 
R

Ron Coderre

Sheesh...Every time I look away from this one I think of a slightly
different approach:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("rngIndependents")) Is Nothing Then
Intersect(Target, Range("rngIndependents")).Offset(RowOffset:=0,
ColumnOffset:=2).ClearContents
End If
End Sub


Regards,
Ron
 
M

malik641

I'm sure the visual basic code works great, but I'm not too familiar
with using that for excel. Is there a way to do this NOT using VB?
 
R

Ron Coderre

What Rajesh asked for can only be done with programming: the clearing of
the dependent list input cells. Regular Excel functions cannot alter
the actual contents of cells or perform other manipulations of the
structure of the workbook (hiding/unhiding, deleting, etc). Of course,
you could just clear the corresponding dependent cell manually. You
could even use conditional formatting to flag that a displayed
dependent value does not relate to the independent value. But that
approach only serves to prompt the user to do the actual changing of
the cell contents.

I hope that answers your question.

Regards,
Ron
 
G

Guigui85

How do you set the default value of the list to the first item instead of
clearing the contents?
 
R

Ron Coderre

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValSrcContents As String
Dim SrcRef As String

If Not Intersect(Target, Range("rngIndependents")) Is Nothing Then
With ActiveCell.Offset(RowOffset:=0, ColumnOffset:=2)
ValSrcContents = .Validation.Formula1
If Mid(ValSrcContents, 2, 1) = "$" Then
SrcRef = Mid(ValSrcContents, 2, InStr(3, ValSrcContents, ":") - 2)
Else
SrcRef = Mid(ValSrcContents, 2, 255)
End If
.value = Range(srcRef).Resize(RowSize:=1, ColumnSize:=1).Value
End With
End If
End Sub


I'm pretty sure it will work. It allows for either range references or
range names. Let me know if it doesn't.

Does that help?

Regards,
Ron
 
T

ThomsonJ

So I'm using this code posted by Ron, thanks btw, to make the dependen
cell blank, if the independent cell is blanked:

If: The independent validation list cells are in A1:A10
The dependent validation list cells are in B1:B10

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Target.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents
End If
End Sub


This works great, but I've created two dependent lists in Excel usin
Data validation. Column B dependent on A and column D dependent on C.

My VB skills are near non-existant. How can I also apply this t
column D dependent on C? I tried copying this code a
Worksheet_Change2 with the appropriate changes, but this did not work.

Thanks
 
Top