Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how blank data validation cell after changing dependent cell?
Thanks for any help.
Using http://contextures.com/xlDataVal02.html, I have a data validation dependent on another data validation. So when I change the original data validation, and then change the dependent data validation, I only get a choice based upon the original data validation. However, before I choose for the dependent data validation, if I had a previous choice that wasn't possible, that value will still remain, even if I choose the original data validation something that won't have that dependent value. Is there a way to blank out the dependent if it won't be allowed? Sorry if that's confusing. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how blank data validation cell after changing dependent cell?
Actually, if I may add to this, how about not 'blanking' it, but setting it
to a default value? Thanks for any help. "Ian Elliott" wrote: Thanks for any help. Using http://contextures.com/xlDataVal02.html, I have a data validation dependent on another data validation. So when I change the original data validation, and then change the dependent data validation, I only get a choice based upon the original data validation. However, before I choose for the dependent data validation, if I had a previous choice that wasn't possible, that value will still remain, even if I choose the original data validation something that won't have that dependent value. Is there a way to blank out the dependent if it won't be allowed? Sorry if that's confusing. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how blank data validation cell after changing dependent cell?
Do you have just 1 primary drop down and 1 dependent drop down? If so, what
cells are they in? -- Biff Microsoft Excel MVP "Ian Elliott" wrote in message ... Thanks for any help. Using http://contextures.com/xlDataVal02.html, I have a data validation dependent on another data validation. So when I change the original data validation, and then change the dependent data validation, I only get a choice based upon the original data validation. However, before I choose for the dependent data validation, if I had a previous choice that wasn't possible, that value will still remain, even if I choose the original data validation something that won't have that dependent value. Is there a way to blank out the dependent if it won't be allowed? Sorry if that's confusing. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how blank data validation cell after changing dependent cell?
Thanks for offering to help, Valko.
No, I have 2 primary & 1 dependent (or maybe the right way to look at it is 1 primary & 2 dependent). The 1st primary is B1, the 1st dependent is B2, and the 2nd dependent is B3. "T. Valko" wrote: Do you have just 1 primary drop down and 1 dependent drop down? If so, what cells are they in? -- Biff Microsoft Excel MVP "Ian Elliott" wrote in message ... Thanks for any help. Using http://contextures.com/xlDataVal02.html, I have a data validation dependent on another data validation. So when I change the original data validation, and then change the dependent data validation, I only get a choice based upon the original data validation. However, before I choose for the dependent data validation, if I had a previous choice that wasn't possible, that value will still remain, even if I choose the original data validation something that won't have that dependent value. Is there a way to blank out the dependent if it won't be allowed? Sorry if that's confusing. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how blank data validation cell after changing dependent cell?
Ok, what you want to do is to clear the contents of the dependent cells
whenever a new selection is made from the parent drop down list. This requires an event macro. Select the sheet with your drop downs Right clcik the sheet tab and select View code Copy paste the code below into the window that opens Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo sub_exit If Target.Address = "$B$1" Then Range("B2:B3").ClearContents ElseIf Target.Address = "$B$2" Then Range("B3").ClearContents End If sub_exit: Application.EnableEvents = True End Sub Close the window and return to Excel -- Biff Microsoft Excel MVP "Ian Elliott" wrote in message ... Thanks for offering to help, Valko. No, I have 2 primary & 1 dependent (or maybe the right way to look at it is 1 primary & 2 dependent). The 1st primary is B1, the 1st dependent is B2, and the 2nd dependent is B3. "T. Valko" wrote: Do you have just 1 primary drop down and 1 dependent drop down? If so, what cells are they in? -- Biff Microsoft Excel MVP "Ian Elliott" wrote in message ... Thanks for any help. Using http://contextures.com/xlDataVal02.html, I have a data validation dependent on another data validation. So when I change the original data validation, and then change the dependent data validation, I only get a choice based upon the original data validation. However, before I choose for the dependent data validation, if I had a previous choice that wasn't possible, that value will still remain, even if I choose the original data validation something that won't have that dependent value. Is there a way to blank out the dependent if it won't be allowed? Sorry if that's confusing. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how blank data validation cell after changing dependent cell?
Another option is to make the first drop down list dependent on the
second list too. There's an example he Prevent Cheating With an Excel Drop Down List http://blog.contextures.com/archives/2009/07/27/ Ian Elliott wrote: Thanks for offering to help, Valko. No, I have 2 primary & 1 dependent (or maybe the right way to look at it is 1 primary & 2 dependent). The 1st primary is B1, the 1st dependent is B2, and the 2nd dependent is B3. "T. Valko" wrote: Do you have just 1 primary drop down and 1 dependent drop down? If so, what cells are they in? -- Biff Microsoft Excel MVP "Ian Elliott" wrote in message ... Thanks for any help. Using http://contextures.com/xlDataVal02.html, I have a data validation dependent on another data validation. So when I change the original data validation, and then change the dependent data validation, I only get a choice based upon the original data validation. However, before I choose for the dependent data validation, if I had a previous choice that wasn't possible, that value will still remain, even if I choose the original data validation something that won't have that dependent value. Is there a way to blank out the dependent if it won't be allowed? Sorry if that's confusing. -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dependent Data Validation | Excel Discussion (Misc queries) | |||
Data Validation list dependent on contents of another cell | Excel Discussion (Misc queries) | |||
dependent lists on data validation | Excel Discussion (Misc queries) | |||
Data Validation and Dependent Lists Q | Excel Worksheet Functions | |||
Blank Cell Validation | Excel Discussion (Misc queries) |