Using Data Validation, I have created a dependent list (based on instructions
found on the Contextures web site:
http://www.contextures.com/xlDataVal02.html).
I want to create a conditional format whereby if someone changes the
selection in the first data validation list without changing the
corresponding selection in the dependent list (i.e., second list), the cell
value in the dependent list will turn red.
Can anyone tell me how to go about doing this?
Thanks, Bob

First, thanks for visiting Debra Dalgleish's Contextures website for
instructions on Dependent Lists. Now, I have a lot less expaining to do.

Since the dependent list is based on a Named List, try this:
With
A1 containing the parent list
B1 containing the dependent list
and the Named list: MyNamedList

Then
Select B1
<format<conditional formatting
Formula is: =ISERROR(MATCH($B$1,MyNamedList,0))
Click the [format] button and set your format
Click the [OK] buttons and you're done.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

Using Data Validation, I have created a dependent list (based on instructions
found on the Contextures web site:
http://www.contextures.com/xlDataVal02.html).
I want to create a conditional format whereby if someone changes the
selection in the first data validation list without changing the
corresponding selection in the dependent list (i.e., second list), the cell
value in the dependent list will turn red.
Can anyone tell me how to go about doing this?
Thanks, Bob

Ron,
Thank you for providing a solution to my problem. Unfortunately, I could
not seem to get it to work. After some lengthly experimentation, I came up
with my own solution:

=ISNA(INDEX(INDIRECT(VLOOKUP($A2,PhaseLookup,2,0)) ,MATCH($B2,INDIRECT(VLOOKUP($A2,PhaseLookup,2,0)), 0),1))

whe
Column A (starting in row 2) contains the in-cell dropdown box for the
parent list
Column B (starting in row 2) contains the in-cell dropdown box for the
dependent list

Please note that since I am "Using Items with Illegal Characters" (refer to
Debra Dalgleish's Contextures website), I had to create a lookup table.

Bob


"Ron Coderre" wrote:

First, thanks for visiting Debra Dalgleish's Contextures website for
instructions on Dependent Lists. Now, I have a lot less expaining to do.

Since the dependent list is based on a Named List, try this:
With
A1 containing the parent list
B1 containing the dependent list
and the Named list: MyNamedList

Then
Select B1
<format<conditional formatting
Formula is: =ISERROR(MATCH($B$1,MyNamedList,0))
Click the [format] button and set your format
Click the [OK] buttons and you're done.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

Using Data Validation, I have created a dependent list (based on instructions
found on the Contextures web site:
http://www.contextures.com/xlDataVal02.html).
I want to create a conditional format whereby if someone changes the
selection in the first data validation list without changing the
corresponding selection in the dependent list (i.e., second list), the cell
value in the dependent list will turn red.
Can anyone tell me how to go about doing this?
Thanks, Bob

Thanks for the feedback, Bob.....I'm glad you got that working.

***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

Ron,
Thank you for providing a solution to my problem. Unfortunately, I could
not seem to get it to work. After some lengthly experimentation, I came up
with my own solution:

=ISNA(INDEX(INDIRECT(VLOOKUP($A2,PhaseLookup,2,0)) ,MATCH($B2,INDIRECT(VLOOKUP($A2,PhaseLookup,2,0)), 0),1))

whe
Column A (starting in row 2) contains the in-cell dropdown box for the
parent list
Column B (starting in row 2) contains the in-cell dropdown box for the
dependent list

Please note that since I am "Using Items with Illegal Characters" (refer to
Debra Dalgleish's Contextures website), I had to create a lookup table.

Bob


"Ron Coderre" wrote:

First, thanks for visiting Debra Dalgleish's Contextures website for
instructions on Dependent Lists. Now, I have a lot less expaining to do.

Since the dependent list is based on a Named List, try this:
With
A1 containing the parent list
B1 containing the dependent list
and the Named list: MyNamedList

Then
Select B1
<format<conditional formatting
Formula is: =ISERROR(MATCH($B$1,MyNamedList,0))
Click the [format] button and set your format
Click the [OK] buttons and you're done.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

Using Data Validation, I have created a dependent list (based on instructions
found on the Contextures web site:
http://www.contextures.com/xlDataVal02.html).
I want to create a conditional format whereby if someone changes the
selection in the first data validation list without changing the
corresponding selection in the dependent list (i.e., second list), the cell
value in the dependent list will turn red.
Can anyone tell me how to go about doing this?
Thanks, Bob

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

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

PERFECT!!! Thanks!

"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

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

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

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

I could be wrong, but I don't think you need to clear the Function cell when
you change the Position cell, if the fields are in sequential order.....

meaning you choose in this order:
1)Select an Asset
2)Select a Function
3)Select a Position

Example using:
Col C Col D Col E
Asset Function Position
Hillsboro Cleaning Day Porter/Matron

You decide you don't want the Day Porter to do cleaning so you erase that
cell.
Do you really want the Cleaning function to erase, too?
Or do you want to leave it there so you can assign somebody else.

However, if you change the Cleaning function to Dog Walking, you'd probably
want the Position to erase.

And...if you change the Asset field....then Function AND Position should
erase.

NOW.....if the fields are NOT in sequential order...why aren't they? <bg

(either way I've got the code for the two scenarios ready to post..Just let
me know which way to go with it)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Toni" wrote:

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

If my suggestions/assumptions are correct and you would adjust cells C1:E1 in
sequential order, then try the below code. :

Note: It might be overkill but this code pops up a nag window to tell the
user to finish selecting items. If it's too annoying, just comment out the
MSGBOX section, below (Leave the SEND KEYS code though).

'--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is one of the Data Validation cells
'But not the last one
If Not Intersect(Target, Range("C1:D1")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "D1").Cells.Count

'Select the cell to the right of the active cell
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select

'Turn off events so you don't get into and endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.Resize(RowSize:=1, ColumnSize:=intCellCount) _
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

ErrTrap:
Application.EnableEvents = True
End Sub
'--------end of code----------

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I could be wrong, but I don't think you need to clear the Function cell when
you change the Position cell, if the fields are in sequential order.....

meaning you choose in this order:
1)Select an Asset
2)Select a Function
3)Select a Position

Example using:
Col C Col D Col E
Asset Function Position
Hillsboro Cleaning Day Porter/Matron

You decide you don't want the Day Porter to do cleaning so you erase that
cell.
Do you really want the Cleaning function to erase, too?
Or do you want to leave it there so you can assign somebody else.

However, if you change the Cleaning function to Dog Walking, you'd probably
want the Position to erase.

And...if you change the Asset field....then Function AND Position should
erase.

NOW.....if the fields are NOT in sequential order...why aren't they? <bg

(either way I've got the code for the two scenarios ready to post..Just let
me know which way to go with it)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Toni" wrote:

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

Thanks, Ron. It works -- sort of. The range is C4:E90. I changed the
target range to C4:E90, but it only works in C4:E10. Thanks for all your
patience and help.

Toni

"Ron Coderre" wrote:

If my suggestions/assumptions are correct and you would adjust cells C1:E1 in
sequential order, then try the below code. :

Note: It might be overkill but this code pops up a nag window to tell the
user to finish selecting items. If it's too annoying, just comment out the
MSGBOX section, below (Leave the SEND KEYS code though).

'--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is one of the Data Validation cells
'But not the last one
If Not Intersect(Target, Range("C1:D1")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "D1").Cells.Count

'Select the cell to the right of the active cell
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select

'Turn off events so you don't get into and endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.Resize(RowSize:=1, ColumnSize:=intCellCount) _
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

ErrTrap:
Application.EnableEvents = True
End Sub
'--------end of code----------

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I could be wrong, but I don't think you need to clear the Function cell when
you change the Position cell, if the fields are in sequential order.....

meaning you choose in this order:
1)Select an Asset
2)Select a Function
3)Select a Position

Example using:
Col C Col D Col E
Asset Function Position
Hillsboro Cleaning Day Porter/Matron

You decide you don't want the Day Porter to do cleaning so you erase that
cell.
Do you really want the Cleaning function to erase, too?
Or do you want to leave it there so you can assign somebody else.

However, if you change the Cleaning function to Dog Walking, you'd probably
want the Position to erase.

And...if you change the Asset field....then Function AND Position should
erase.

NOW.....if the fields are NOT in sequential order...why aren't they? <bg

(either way I've got the code for the two scenarios ready to post..Just let
me know which way to go with it)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Toni" wrote:

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

Try this:

'--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is one of the Data Validation cells
'But not the last one
If Not Intersect(Target, Range("C4:D90")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "D4").Columns.Count

'Select the cell to the right of the active cell
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select

'Turn off events so you don't get into an endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.Resize(RowSize:=1, ColumnSize:=intCellCount) _
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

ErrTrap:
Application.EnableEvents = True
End Sub
'--------end of code----------

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Toni" wrote:

Thanks, Ron. It works -- sort of. The range is C4:E90. I changed the
target range to C4:E90, but it only works in C4:E10. Thanks for all your
patience and help.

Toni

"Ron Coderre" wrote:

If my suggestions/assumptions are correct and you would adjust cells C1:E1 in
sequential order, then try the below code. :

Note: It might be overkill but this code pops up a nag window to tell the
user to finish selecting items. If it's too annoying, just comment out the
MSGBOX section, below (Leave the SEND KEYS code though).

'--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is one of the Data Validation cells
'But not the last one
If Not Intersect(Target, Range("C1:D1")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "D1").Cells.Count

'Select the cell to the right of the active cell
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select

'Turn off events so you don't get into and endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.Resize(RowSize:=1, ColumnSize:=intCellCount) _
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

ErrTrap:
Application.EnableEvents = True
End Sub
'--------end of code----------

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I could be wrong, but I don't think you need to clear the Function cell when
you change the Position cell, if the fields are in sequential order.....

meaning you choose in this order:
1)Select an Asset
2)Select a Function
3)Select a Position

Example using:
Col C Col D Col E
Asset Function Position
Hillsboro Cleaning Day Porter/Matron

You decide you don't want the Day Porter to do cleaning so you erase that
cell.
Do you really want the Cleaning function to erase, too?
Or do you want to leave it there so you can assign somebody else.

However, if you change the Cleaning function to Dog Walking, you'd probably
want the Position to erase.

And...if you change the Asset field....then Function AND Position should
erase.

NOW.....if the fields are NOT in sequential order...why aren't they? <bg

(either way I've got the code for the two scenarios ready to post..Just let
me know which way to go with it)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Toni" wrote:

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

Perfect! Thank you so much. Toni

"Ron Coderre" wrote:

Try this:

'--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is one of the Data Validation cells
'But not the last one
If Not Intersect(Target, Range("C4:D90")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "D4").Columns.Count

'Select the cell to the right of the active cell
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select

'Turn off events so you don't get into an endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.Resize(RowSize:=1, ColumnSize:=intCellCount) _
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

ErrTrap:
Application.EnableEvents = True
End Sub
'--------end of code----------

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Toni" wrote:

Thanks, Ron. It works -- sort of. The range is C4:E90. I changed the
target range to C4:E90, but it only works in C4:E10. Thanks for all your
patience and help.

Toni

"Ron Coderre" wrote:

If my suggestions/assumptions are correct and you would adjust cells C1:E1 in
sequential order, then try the below code. :

Note: It might be overkill but this code pops up a nag window to tell the
user to finish selecting items. If it's too annoying, just comment out the
MSGBOX section, below (Leave the SEND KEYS code though).

'--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is one of the Data Validation cells
'But not the last one
If Not Intersect(Target, Range("C1:D1")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "D1").Cells.Count

'Select the cell to the right of the active cell
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select

'Turn off events so you don't get into and endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.Resize(RowSize:=1, ColumnSize:=intCellCount) _
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

ErrTrap:
Application.EnableEvents = True
End Sub
'--------end of code----------

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I could be wrong, but I don't think you need to clear the Function cell when
you change the Position cell, if the fields are in sequential order.....

meaning you choose in this order:
1)Select an Asset
2)Select a Function
3)Select a Position

Example using:
Col C Col D Col E
Asset Function Position
Hillsboro Cleaning Day Porter/Matron

You decide you don't want the Day Porter to do cleaning so you erase that
cell.
Do you really want the Cleaning function to erase, too?
Or do you want to leave it there so you can assign somebody else.

However, if you change the Cleaning function to Dog Walking, you'd probably
want the Position to erase.

And...if you change the Asset field....then Function AND Position should
erase.

NOW.....if the fields are NOT in sequential order...why aren't they? <bg

(either way I've got the code for the two scenarios ready to post..Just let
me know which way to go with it)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Toni" wrote:

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

Hi , I have used the Debra Dalgleish's Contextures website for
instructions on Dependent Lists, and it works excellent , but now I need to add items to my validation Lists , let me explain , if the user type a new element I need to add that new one to the validation list. How can I do it ?

Thank you

"Ron Coderre" wrote:

First, thanks for visiting Debra Dalgleish's Contextures website for
instructions on Dependent Lists. Now, I have a lot less expaining to do.

Since the dependent list is based on a Named List, try this:
With
A1 containing the parent list
B1 containing the dependent list
and the Named list: MyNamedList

Then
Select B1
<format<conditional formatting
Formula is: =ISERROR(MATCH($B$1,MyNamedList,0))
Click the [format] button and set your format
Click the [OK] buttons and you're done.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

Using Data Validation, I have created a dependent list (based on instructions
found on the Contextures web site:
http://www.contextures.com/xlDataVal02.html).
I want to create a conditional format whereby if someone changes the
selection in the first data validation list without changing the
corresponding selection in the dependent list (i.e., second list), the cell
value in the dependent list will turn red.
Can anyone tell me how to go about doing this?
Thanks, Bob

I'd like to help, but I'll be traveling with no internet access for the next
10 days. Since the original thread is so old.....try posting a new question
so the excellent talent that frequents these groups can help you. (If you do
that...leave a post here indicating that, so you don't have 2 separate groups
of people helping)
***********
Regards,
Ron

XL2003, WinXP


"Angeles" wrote:

Hi , I have used the Debra Dalgleish's Contextures website for
instructions on Dependent Lists, and it works excellent , but now I need to add items to my validation Lists , let me explain , if the user type a new element I need to add that new one to the validation list. How can I do it ?

Thank you

"Ron Coderre" wrote:

First, thanks for visiting Debra Dalgleish's Contextures website for
instructions on Dependent Lists. Now, I have a lot less expaining to do.

Since the dependent list is based on a Named List, try this:
With
A1 containing the parent list
B1 containing the dependent list
and the Named list: MyNamedList

Then
Select B1
<format<conditional formatting
Formula is: =ISERROR(MATCH($B$1,MyNamedList,0))
Click the [format] button and set your format
Click the [OK] buttons and you're done.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

Using Data Validation, I have created a dependent list (based on instructions
found on the Contextures web site:
http://www.contextures.com/xlDataVal02.html).
I want to create a conditional format whereby if someone changes the
selection in the first data validation list without changing the
corresponding selection in the dependent list (i.e., second list), the cell
value in the dependent list will turn red.
Can anyone tell me how to go about doing this?
Thanks, Bob

Angeles

Debra Dalgleish has a sample workbook for adding items to a DV list.

DV0012 - Update Validation List -- type a new value in a cell that contains data
validation, and it's automatically added to the source list, and the list is
sorted; a macro automates the list updates. DataValListAddSort.zip 11 kb

http://www.contextures.on.ca/excelfiles.html#DataVal

Scroll down until you find DV0012 to download the file.


Gord Dibben MS Excel MVP

On Wed, 3 Oct 2007 19:05:01 -0700, Ron Coderre
wrote:

I'd like to help, but I'll be traveling with no internet access for the next
10 days. Since the original thread is so old.....try posting a new question
so the excellent talent that frequents these groups can help you. (If you do
that...leave a post here indicating that, so you don't have 2 separate groups
of people helping)
***********
Regards,
Ron

XL2003, WinXP


"Angeles" wrote:

Hi , I have used the Debra Dalgleish's Contextures website for
instructions on Dependent Lists, and it works excellent , but now I need to add items to my validation Lists , let me explain , if the user type a new element I need to add that new one to the validation list. How can I do it ?

Thank you

"Ron Coderre" wrote:

First, thanks for visiting Debra Dalgleish's Contextures website for
instructions on Dependent Lists. Now, I have a lot less expaining to do.

Since the dependent list is based on a Named List, try this:
With
A1 containing the parent list
B1 containing the dependent list
and the Named list: MyNamedList

Then
Select B1
<format<conditional formatting
Formula is: =ISERROR(MATCH($B$1,MyNamedList,0))
Click the [format] button and set your format
Click the [OK] buttons and you're done.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

Using Data Validation, I have created a dependent list (based on instructions
found on the Contextures web site:
http://www.contextures.com/xlDataVal02.html).
I want to create a conditional format whereby if someone changes the
selection in the first data validation list without changing the
corresponding selection in the dependent list (i.e., second list), the cell
value in the dependent list will turn red.
Can anyone tell me how to go about doing this?
Thanks, Bob