Clear content of cell, based upon contents of two other cells?

[stockstradr]

Please forgive what I'm sure is a "newbie" question, but you see I'm just starting to program Excel spreadsheets so have a lot to learn. I'm a principle staff mechanical engineer working for a large OEM, and I'm revising a tolerance analysis spreadsheet that I previously created, that is used by mechanical engineers worldwide at my employer.

Thank you in advance for your kind advice.

I'm using Excel 2003, but I also hope the solution will also work when sheet is imported into Excel 2010.

BACKGROUND:
I have a cell, call it "A1", where user input is filtered by a typical uncomplicated Data Validation drop-down list (Data > Validation > Settings Validation Criteria Allow: LIST). You might imagine the user clicks the "A1" cell and a drop-down pops up for that cell, forcing the user to select from "1.0" or "2.0" or "3.0" and then the cell takes the chosen value.

That part is working fine.

QUESTION:
Based upon the contents of two other nearby cells (direct data entry cells, not governed by a Data Validation drop-down list), call those cells "B1" and "C1", I want a function (or macro) that will CLEAR any data (not the formatting) that shows in the "A1" cell.

Imagine that the user has select a value (1.0, or 2.0, or 3.0) from the "A1" drop-down selection list, and now that value appears in "A1."
Next you might imagine the user clears the contents of cells "B1" or "C1" and now I want my spreadsheet to immediately CLEAR the user's choice from cell "A1" because they have deleted the contents from either of cells "B1" or "C1." You see, having a value in "A1" is only meaningful IF there is also a value in either of cells "B1" or "C1"

Obviously, you could view this as "reset function" for cell "A1" based on the contents of cells "B1" or "C1"

I'm wondering if there is some nifty Excel function that will do this, or if I need to handle this with a simple macro that uses .ClearContents?

To anticipate your possible follow-up question/solution:
YES, I already have the content of the list Source cells containing the drop-down list (1.0, 2.0, 3.0) CONDITIONAL so they go BLANK if they do not find data in either cell "B1" or "C1." In other words, if the user hasn't entered (or deletes) the values in either cell "B1" or "C1", then when they click on cell "A1" it is correctly working that the drop-down list for "A1" shows only BLANKS (because the 3-cell source cells have been conditionally blanked). Now if they then enter something into either cell "B1" or "C1" and then go to click "A1" they will find the drop-down Data Validation list for that cell now correctly lists the choices (1.0, 2.0, 3.0), so that aspect is working correctly.

However, the problem is if a data (1.0, 2.0, 3.0) already appears in cell "A1" and then the user deletes values in either of cell "B1" or "C1", I want the spreadsheet to automatically CLEAR that previously entered value from showing in cell "A1" but I'm not sure how to do that?

 

[syntaxed]

You could add a small worksheet change macro for that.

Steps:
1. Right-click the sheet and left-click "View Code"
2. Paste the following code in the window that appears:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("B1,C1")) Is Nothing Then
        If Range("B1") = "" Or Range("C1") = "" Then
            Range("A1").ClearContents
        End If
    End If
End Sub

Now whenever the user clears the contents of cells B1 or C1, the contents of cell A1 will be cleared automatically.

If you want to change the cell references, you can replace B1, C1, and A1 in the code.

 

[stockstradr]

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B1,C1")) Is Nothing Then
If Range("B1") = "" Or Range("C1") = "" Then
Range("A1").ClearContents
End If
End If
End Sub

Thank you! This code works great!
I really appreciate that experts such as you and others on this great forum are willing to share your valuable time, and post these helpful tips for us newbies. Have a nice weekend!









</PRE>