Data Validation

Hi,

Resetting the dependent DropDown is something you must do with your sheet, via Event code that behaves: when A1 changes, reset all dependent DropDowns. This can be accomplished in VBA in that sheet's VBA Code module in the WorkSheet_Change event.

How is your Data Validation LIST referenced in your dependent DropDown?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

Skip,

I appreciate the feedback. I am on the road with the spreadsheet I was using. When I get back to the office I will post more details.

Thank you


Macmet

Standing by.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

Wow, I cannot believe this been a month. But I am back using the sheet again.

I am using the Indirect function in my dependent pull down menu.

For example I have my first pulldown menu in C9 with the reference to my TASKS list.

The second pull down menu is in E9 I have,

=INDIRECT($C$9)

Then then I also have another pulldown in F9 where it is,

=INDIRECT($E$9)

This works when I go through and make the selections in the proper order. If I want to go back and change the selection picked in the list in C9, the selections made in E9, F9 stay the same. What would be great would be any time I changed the selection in C9, the cells in E9, F9 would go blank. And also if I were to change the selection in E9 only, the F9 cell would go blank.

I hope that makes sense.


Macmet

You're going to have to use VBA to do this. This is done with Change Event in the Excel sheet.

Here's the way to find these Events:
[tt]
1) Right-click the Sheet TAB and select View Code. This exposes the Code Sheet in the VBA Editor for this Sheet. (Alt+F11 will toggle between the Sheet and the VBA Editor.)
2) Just above and on the left-hand side, is the Object Drop Down. You'll see (General) initially. Click the Drop Down and select Worksheet.
3) Just above and on the right-hand side, is the Procedure Drop Down. You'll see Selection Change initially. Click the Drop Down and select Change, because we want to do something when some value changes in C9, your initial Drop Down.

This is what you'll see in the Code Sheet...
So here's what we need to happen: When you make a new selection, ie change the value in C9, the contents of E9 & F9 need to be cleared.
So although this event fires any time ANYTHING changes value in this sheet, the ClesrContents only occurs when The Target coincides with C9.
[/tt]

Give the E9 change a try yourself and see if you can make that work. Post back if you need more help.

Hope that works for you.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

Skip,

That works exactly how I wanted it.

Does that code become part of this spreadsheet? I.e. if I send it to my colleagues, will the ClearContents work for them or do they need to do that VBA code?

Thanks,
Macmet

It is in the sheet. However, they will need to Enable Macros to get this functionality.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}