I got that part. The problem is that a cell value can only come from
one source: formula or manual input. It cannot be both. In other
words, if you have the table you show in A1, then A2 would be a
manually entered value. B2 and C2 would have calculated values by
formula.
Along the lines of:
B2 = IF(OR(A2,C2),0)
What's not clear is what the formula should evaluate to if B2 is 0.
How would you determine whether Y or Z should be 1?
Another approach would be to do this programmatically. Let's say you
have an event trigger in your worksheet's code module, as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A2:C4")) Is Nothing Then
Dim rng As Excel.Range
Set Target = Target.Cells(1)
If Target.Value = 1 Then
For Each rng In Intersect(Target.EntireRow, Me.Range("A2:C4"))
If rng.Address <> Target.Address Then
rng.Value = 0
End If
Next rng
End If
End If
End Sub
Any time a value of 1 is entered in range A2:C4, the other 2 values in
the same row are set to 0.
Again, there must be greater utility to doing this, but I cannot
gather what it is from your inquiry.