Data Validation - Dependent Lists & Conditional Formatting
Sorry if I was unclear.
C is the parent of D and D is the parent of E. If, after items are selected
for C, D, and E and the user deletes the item in Col C, I would like Cols D
and E to be blank. If the user deletes a selected item in Col E, I would
like Col D to be blank.
Col C Col D Col E
Asset Function Position
Hillsboro Cleaning Day Porter/Matron
Hillsboro Audiovisual Audiovisual Technician
Sorry for the confusion. Thank you for your patience.
Toni
"Ron Coderre" wrote:
I want to make sure that what you're asking for is what you really need.
(I'll assume all of the DV lists are on Row_2)
You indicated that a change to either D2 or E2 causes a change in C2, the
parent list, but no other changes.
Typically, in the scenario you described.....
whe
C2 is the parent DV list cell
D2 is dependent on C2
E2 is dependent on D2
then:
changing E2 has no impact on C2 and D2
changing D2 changes E2, but not C2
changing C2 changes both D2 and E2
Could you restate the request, with plenty of details, so can receive a
better quality response?
***********
Regards,
Ron
XL2002, WinXP
"Toni" wrote:
This worked for me as well. Thank you very much. Now I have a related
question
My ParentList is in col C. Col D is dependent upon C. Col E is dependent on
D. How can I modify the code so that when some deletes data from Col D or E,
Col C Parent List goes blank?
Thanks for your help.
Toni
"Ron Coderre" wrote:
You'd need VBA code to effect what you want.
Try working with this and post back with any questions
Assumptions:
Parent List cells are in A1:A10
Dependent List cells are in B10
Copy the below VBA code into the sheet module of the worksheet with the Data
Validation.
You get there this way:
Right-Click on the sheet tab
Select "View Code"
'----------Start of Code------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngAllParentCells As Range
Dim rngDepCells As Range
Dim rngCell As Range
Set rngAllParentCells = Range("A1:A10")
Set rngDepCells = Intersect(Target, rngAllParentCells)
If Not rngDepCells Is Nothing Then
For Each rngCell In rngDepCells.Cells
'Move 1 cell to the right and clear contents
rngCell.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents
Next rngCell
End If
Set rngAllParentCells = Nothing
Set rngDepCells = Nothing
Set rngCell = Nothing
End Sub
'----------End of Code------------
Now....whenever one of the A1:A10 DV cells is changed,
the corresponding B1:B10 cell is cleared
Does that help?
***********
Regards,
Ron
XL2002, WinXP
"Shelly" wrote:
I used Ron's formula below, with success, but I would like something little
different.
I would like the B1 cell (dependent list) to be blank if the A1 cell is
changed after the B1 cell has been completed.
So, a user enters a value in A1, the list in B1 is restricted. They select
something from B1. They they go back to A1 and either change the value or
delete the value - at this point I would like B1 to go blank.
Any ideas?
THANKS
|