Excel - how to either have direct data input or cell value from formula

Solution 1:

As soon as a value is entered into a cell, any formula in that cell will be overwritten. You can use a VBA routine to restore the formula, though. Your requirements should include the logic for what has precendence, i.e. in what case should a formula be used and in what case does the manually entered number "win".

The following code works on these premises:

• either cell A2 or A3 get changed
• Both A2 and A3 must have values

So, when either A2 or A3 are edited AND both cells contain a value, the macro will write =A2+A3 into cell A4. If any of the values in A2 or A3 are text, the macro will still run and Excel will show the error message for adding text with numbers. Use Sum() to avoid the error message.

If cell A4 is then edited and the formula is overwritten, this value will show until either A2 or A3 are edited again.

If that does not match your requirements, please spell them out in more detail.

The code goes into the Sheet module (right-click the sheet tab, click "View code", paste code into the big code area.)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A2:A3")) Is Nothing Then
    If WorksheetFunction.Count(Range("A2:A3")) = 2 Then
        Range("A4").Formula = "=A2+A3"
    End If
End If

End Sub