replacing text from a formula in another cell

[disisme]

I seek to interrogate the value in a cell (integer 0 or 1), and then arbitrarily force a text value into another free text entry cell.  Example.  A1 has an integer value that is unknown (and not relevant to be known).  B1 has a value 0, or 1.  I need a formula that will force A1 to 0, if B1 is 1.  If B1 is 0, A1s values arent changed, irrespective of whether they have changed or not.  Since A1 is guaranteed to change dynamically, it cannot contain a formula.

[#2]

@disisme 

This requires VBA code (so it'll work only in the desktop version of Excel for Windows and Mac).

In your example:

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B1").Value, Target) Is Nothing Then
        If Range("B1").Value = 1 Then
            Application.EnableEvents = False
            Range("A1").Value = 0
            Application.EnableEvents = True
        End If
    End If
End Sub

P.S. This assumes that the user enters 0 or 1 in cell B1. If B1 contains a formula that returns 0 or 1, use

Private Sub Worksheet_Calculate()
    If Range("B1").Value = 1 Then
        Application.EnableEvents = False
        Range("A1").Value = 0
        Application.EnableEvents = True
    End If
End Sub

[#3]

Awesome. I didnt expect this to be a sheet specific thing, was hoping it could be cell specific because I Want to do this one cell update, to force values into a number of cells. Could I repeat line 5 above for each of the cells I want to reset?

[#4]

This actually generates a "Run-time error '424'...the explanation of which might as well be written in Mandarin for all the sense it makes. Error 424 says "object required" and the error line in debug is line 2.. "If Not Intersect(Range("E2").Value, Target) Is Nothing Then" (I am actually using E2 as the trigger and D2 as the data cell to change)

[#5]

Note this error 424 comes up no matter what value I put in E2 (0 or 1).  Correction.  The error comes up if I change the value in ANY cell.

[#6]

@disisme 

My apologies, it was air code. It should have been

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("E2"), Target) Is Nothing Then
        If Range("E2").Value = 1 Then
            Application.EnableEvents = False
            Range("D2").Value = 0
            Application.EnableEvents = True
        End If
    End If
End Sub

[#7]

perrrrfect. Repeating line 5 with relevant cell number changes clears the sheet..awesome. thanks so much Hans