clearing the contents of one cell, based on another cell

DMT

New Member
Joined
Jan 21, 2004
Messages
28
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.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
You cannot clear the cell, but you can make it appear blank. i.e.
Code:
=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

Code:
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

New Member
Joined
Jan 21, 2004
Messages
28
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.
 

DMT

New Member
Joined
Jan 21, 2004
Messages
28
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,316
Messages
5,600,910
Members
414,415
Latest member
joshuaba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top