Data Validation
Data Validation
(OP)
Hello Everyone,
I am fairly new to using drop down menus in Excel, but I have managed to get a few working and have them linked so the drop down menu is dependent on an initial selection. For example, you can see the webpage here I am using to make the conditional lists.... -> http://blogs.office.com/2009/11/24/create-conditio...
My only issue is that if I make an initial section in A1, which gives me a drop down menu in B1 based on the selection in A1. After I make the selection in B1, if I go back and change A1, it is possible that I am left with an "impossible" combination. I believe this is what was being discussed in the older thread below.
thread770-165533: Drop Down Lists
I'm wondering if it is possible in the newer versions in Excel to automatically change or clear the selection in B1 if A1 is changed after.
I hope that makes sense. I can provide screenshots if I need to provide more clarification.
Cheers,
Macmet
I am fairly new to using drop down menus in Excel, but I have managed to get a few working and have them linked so the drop down menu is dependent on an initial selection. For example, you can see the webpage here I am using to make the conditional lists.... -> http://blogs.office.com/2009/11/24/create-conditio...
My only issue is that if I make an initial section in A1, which gives me a drop down menu in B1 based on the selection in A1. After I make the selection in B1, if I go back and change A1, it is possible that I am left with an "impossible" combination. I believe this is what was being discussed in the older thread below.
thread770-165533: Drop Down Lists
I'm wondering if it is possible in the newer versions in Excel to automatically change or clear the selection in B1 if A1 is changed after.
I hope that makes sense. I can provide screenshots if I need to provide more clarification.
Cheers,
Macmet
Talk To Other Members
RE: Data Validation
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!
RE: Data Validation
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
RE: Data Validation
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Data Validation
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
RE: Data Validation
Here's the way to find these Events:
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...
CODE
CODE
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Cells(9, "C")) is nothing Then Cells(9, "E").ClearContents Cells(9, "F").ClearContents End If End SubGive 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,
for a NUance!
RE: Data Validation
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
RE: Data Validation
Skip,
Just traded in my OLD subtlety...
for a NUance!