Dependent Lists in Excel
 

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


--
rajeshkumar
------------------------------------------------------------------------
rajeshkumar's Profile: http://www.excelforum.com/member.php...o&userid=24672
View this thread: http://www.excelforum.com/showthread...hreadid=382451

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=382451

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


--
rajeshkumar
------------------------------------------------------------------------
rajeshkumar's Profile: http://www.excelforum.com/member.php...o&userid=24672
View this thread: http://www.excelforum.com/showthread...hreadid=382451

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=382451

Ron,
Exactly what I want.

Thanks a lot Ron.

Rajesh


--
rajeshkumar
------------------------------------------------------------------------
rajeshkumar's Profile: http://www.excelforum.com/member.php...o&userid=24672
View this thread: http://www.excelforum.com/showthread...hreadid=382451

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?


--
rajeshkumar
------------------------------------------------------------------------
rajeshkumar's Profile: http://www.excelforum.com/member.php...o&userid=24672
View this thread: http://www.excelforum.com/showthread...hreadid=382451

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 named
range:

InsertNameDefine
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.


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?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=382451

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
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=382451

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=382451

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?


--
malik641
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=382451

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=382451

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

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

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

"
wrote in message ...
How do you set the default value of the list to the first item instead of
clearing the contents?

Dependent Lists in Excel
 

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

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 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.


--
ThomsonJ
------------------------------------------------------------------------
ThomsonJ's Profile: http://www.excelforum.com/member.php...o&userid=36335
View this thread: http://www.excelforum.com/showthread...hreadid=382451