clearing the contents of one cell, based on another cell

[DMT]

Would someone be able to show me how to clear the contents of a cell based on the value of another cell. For example, cell A1 is linked to some criteria and returns either "True" or "False". I would like cell C1 cleared of its contents every time cell A1's value is "False". Is this possible? Also, if I have more than one of these True/False cells and dependent cells (that need to be cleared based on the correpsonding True/False cell and its value), could the code be written for more than one cell, so that all if there is a False in any of the Column A values then the cell next to the False in Column C is cleared?

I hope this makes sense. Thanks a lot.

 

[DRJ]

You cannot clear the cell, but you can make it appear blank. i.e.

=If(A1=False,"",A1)

If you really need the cell to be cleared and not just look blank you will need to use vba code. Try a worksheet_change event

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column > 1 Then
        Else
        If Target.Value = False Then 
            Target.Offset(0,2).ClearContents
            Else
       End If
    End If

End Sub

 

[DMT]

Thanks,

I see how the code works, but i would like this to work when the cell that has a "False" in it is not an entry cell, but contains an if statement that returns "False" if cetain criteria are met. Is this possible? Thanks again.

 

[DRJ]

Yeah thats possible, what is the criteria?

 

[DMT]

I have two different situations.

The first situation is:

I have one drop-down menu with three choices. There is a second dropdown menu next to the first one. I am using the data validation lists (not the forms combo box). The second dropdown menu's contents depend on the first dropdown menu's choice. If choice 1 or 2 are chosen in the first dropdown, there is no choice in the second dropdown. If choice #3 is chosen in the first dropdown, then there is a choice of 5 categories in the second dropdown. The problem is if choice #3 is picked and then a category from the second is chosen, then someone changes their mind and changes the first dropdown to choice #1, the second dropdown still shows the intial choice, even though choice #1 should not allow any choices.

I can't seem to use the indirect formula because one of my categories name includes an "&" and this is not allowed when defining a name. So, instead, I am using a the offset function to change the range of the inputs for the list.

The second situation is where is I have two blank cells. Next to these two blank cells is a third cell that is a dropdown menu. The contents of this dropdown depend on whether something is entered in "blank cell #1" or "blank cell #2". If blank cell #1 is filled in (basically anything greater than 0), then the list corresponds to that choice. And if blank cell #2 is filled in, the contents of the list in the dropdown adjusts for that choice. Both blank cell 1 and 2 cannot be both filled in.....only one. So, the problem lies in the fact that if someone fills in blank cell #1, then chooses from the dropdown, and then goes back, changes their mind, deletes the contents of blank cell 1 and enters something in blank cell 2, the dropdown cell still shows the choice orginally made, even though it does not match the choices for blank cell 2.

I hope this all makes sense! If not please let me know and I'll try and explain it better.

So, if I could have the dropdown menus in both cases be cleared if the choice doens't match what is appropriate for the situation (in the first case, based on the choice in the first dropdown..........and in the second case, based on the entry in blank cell 1 or 2).

Thanks!