Data validation clearing out other cells

[Metaripley]

I have got a sheet where there are a couple of cell that have a list in data validation.

Some data validations are dependable on others.
Works great until you first select the second one and dont change the first one. Then you get a result I dont want to allow.

Example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]List A:[/TD]
[TD]List B-1:[/TD]
[TD]List B-2:[/TD]
[TD]List C-1:[/TD]
[TD]List C-2:[/TD]
[/TR]
[TR]
[TD]MM[/TD]
[TD]30x30
[/TD]
[TD]1.5"x1.5"[/TD]
[TD]M8[/TD]
[TD]5/16"[/TD]
[/TR]
[TR]
[TD]Inch[/TD]
[TD]40x40[/TD]
[TD]2"x2"[/TD]
[TD]M10[/TD]
[TD]3/8"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]45x45[/TD]
[TD][/TD]
[TD]M12[/TD]
[TD]1/2"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]50x50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

When I select MM in list A, List B-1 shows and C-1.
Lets say I selected MM, 40x40, M10, but then select Inch, 40x40 and M10 are still there.
The outcome would be Inch, 40x40, M10. And this is incorrect.

Any way to fix this?

 

[wideboydixon]

You would need to use VBA to catch the Worksheet_Change() event and clear out the other cells. How do you have this set up to change the data validation options?

WBD

 

[Metaripley]

With a IF formula and some defined lists.

 

[wideboydixon]

Right click on the tab name and select "View Code". Paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then Exit Sub

If Target.Address = "$A$1" Then ' Change address as appropriate
    Range("B1").ClearContents ' Change address as appropriate
    Range("C1").ClearContents ' Change address as appropriate
End If

End Sub

Change "$A$1" to be the cell that contains the MM/Inch pick list. Change "B1" and "C1" to the cells you want to clear.

Hope that helps.

WBD

 

[Metaripley]

Thanks! Works like a charm.