Dependent Lists in Excel

[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

[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:

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

Switch back to the worksheet and test.

Does that help?
Ron

[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

[Ron Coderre]

If:
The independent validation list cells are in A1:A10
The dependent validation list cells are in C1:C10 then try this:
[vba]
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
[/vba]
Does that help?

Ron

[rajeshkumar]

Ron,
Exactly what I want.

Thanks a lot Ron.

Rajesh

[rajeshkumar]

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

Can anyone help me out in this?

[Ron Coderre]

Here's something to try:
[vba]
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
[/vba]

However, as coded above, inserting rows won't change the A1:A10 test. You should consider replacing that hardcoded reference with a named 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 (see below) will still work as designed.

[vba]
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
[/vba]

Does that help?

Ron

[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

[Ron Coderre]

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

[vba]
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
[/vba]

Regards,
Ron

[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?

[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

[rajeshkumar]

Ron,
The code is working fine.
Thanks a lot.

Rajesh

[[email protected]]

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







"rajeshkumar" wrote:

>
> Ron,
> The code is working fine.
> Thanks a lot.
>
> Rajesh
>
>
> --
> rajeshkumar
> ------------------------------------------------------------------------
> rajeshkumar's Profile: http://www.excelforum.com/member.php...o&userid=24672
> View this thread: http://www.excelforum.com/showthread...hreadid=382451
>
>

[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

"[email protected]" <[email protected]@discussions.microsoft.com>
wrote in message news:[email protected]...
> How do you set the default value of the list to the first item instead of
> clearing the contents?

[ThomsonJ]

So I'm using this code posted by Ron, thanks btw, to make the dependent 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
[vba]
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
[/vba]

This works great, but I've created two dependent lists in Excel using 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 to column D dependent on C? I tried copying this code as Worksheet_Change2 with the appropriate changes, but this did not work.

Thanks.