Blank a cell if the contents of another cell deleted

[girapas]

Say that in every cell (A1 to A10) of a sheet, I select an item with Data Validation / List. The list is a named one (say 'List1').
After each selection I enter data (only numbers) to the adjacent cells (the same row, columns B to D). What I want is when I delete the content of cells B1 to D10, the value in cells A1 to A10 to be automatically blank. I've tried in another column (L) to enter formulas like these without success:
=IF(B1=0,$A1="")
=IF(B1=0,$A1=0)
=IF(ISBLANK(B1),$A1="")

Notice that I cannot use Conditional Formatting because I've used all Conditions in the whole range A1:D10.

 

[Cloverken]

An Excel Function cannot perform an action like clearing the contents of another cell. However you can use the Worksheet_Change event with Visual Basic to perform what you need. Simply copy the following code and open your worksheet then press Alt+F11 and paste the code in the Right-Side open pane. Then close the Visual Basic Editor and test it out.

Private Sub Worksheet_Change(ByVal Target As Range)
      Dim RangeA As Range
      Set RangeA = Range("B1:D10")
        On Error Resume Next
            If Application.CountBlank(RangeA) = RangeA.Cells.Count Then
                Range("A1:A10").ClearContents
            Else
                Exit Sub
            End If
      Set RangeA = Nothing
End Sub

 

[girapas]

Thanks or your reply.