Clearing Cells when changing dropdown list choice

[inq80]

Hi,

Can someone point me in the right direction of this please?

I've got four drop down lists which each populate dependent on the option chosen in the one below. The problem is once populated, if you go back to the previous box and change the option you pick, the next box keeps continues to display the option picked previously which technically should no longer exist.

How do i get the menu in question to clear when the box prior has a new option chosen?

thanks

[protonLeah]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[FDibbins]

As far as I know, there is no regular formula that will "zero out" the other cells, but you could perhaps use Conditional Formatting to change the cell color if its contents doesnt match what it should?

[inq80]

Hi,

Thanks for the replies - i hope there is an answer to this as it just looks unfinished without it

The example is attached. As you will see, if you pick 1 from the list in box 1, then A in box 2, it allows you to pick a1 from box 3.

problem is if you then go back to box 2 and change your pick to B, box 3 remains as a1 but that should no longer be an option. How do you get then to clear?

[FDibbins]

This will change teh cell color (to your choise)...

1. highlight the range you want to apply the conditional formatting to (F6:F7)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =ISERROR(MATCH(F6,INDIRECT(F5),0)) format fill RED (or whatever you want)

You could then include an instruction to check for RED

[inq80]

Thanks FDibbins.

Not quite the solution I'm looking for but will be my default position shortly if an auto clear cannot be found.

[FDibbins]

I know this can be done with VBA, but not with a regular formula. If you put a formula in teh DD cell, it will be replaces the very 1st time you use the DD

[inq80]

i should really learn VBA at some point...

[protonLeah]

Please Login or Register  to view this content.

[inq80]

Hi ProtonLeah,

Thanks a great piece of code and works fantastically in my example.

My main workbook though is trying to do the same thing with 4 option boxes: E4, E6, E8 & E10 so i changed it to below and it's not work apart from E4 changes E6 - all the rest don't do anything. Have i missed something?:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case Is = "$E$4"
Target.Offset(2, 0).Resize(5, 1).Value = ""
Case Is = "$E$6"
Target.Offset(2, 0).Resize(3, 1).Value = ""
Case Is = "$E$8"
Target.Offset(2, 0).Value = ""
End Select
End Sub

[Tony Valko]

Just a passing observation...

This "side effect" is almost always overlooked when folks ask for dependent drop down lists!

[inq80]

Just a passing observation...

This "side effect" is almost always overlooked when folks ask for dependent drop down lists!

it drive me nuts! does my OCD no good at all...

[inq80]

Ignore me. It's now working fine. My computer must have been having a moment. Thanks protonleah