1

I would like to either populate a cell value from direct key input or have the cell value derived from a formula.

Example: either directly input a number into cell A4 or have the value for A4 = A2 + A3

I don't want the formula overwritten should direct key input be used.

Thx, hope this clarifies.

  • I don't think that's possible... – Jerry Sep 23 '13 at 19:30
  • 1
    I believe that what you seem to be asking for is impossible. The closest you can come to doing that is to have two cells; use A1, for example, for input, and then set B1 to =IF(A1<>"", A1, (formula)). – Scott Sep 23 '13 at 19:32
3

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
0

VBA can do it, it needs to be in the worksheet VBA and might need some tweaking

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Count = 1 Then
            If .Column = 4 And .Row = 1 Then
                If IsEmpty(.Value) And .Offset(-1) <> "" AND .Offset(-2) <> "" Then
                    Application.EnableEvents = False
                    .FormulaR1C1 = "=A2+A3"
                    Application.EnableEvents = True
                End If
            End If
        End If
    End With
End Sub

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.