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

microsoft excelworksheet-function

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.

Best Answer

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

Related Solutions

Microsoft Excel 2010 – Can Formula-Input Data Be Used for Cell References?

The first part of your question can be solved by using the INDIRECT function. If cell A3 contains this formula =INDIRECT(A4) then it will return the value of the cell specified in A4.

You could put a formula like this =A5&"!A1" together in cell A7 and then use an INDIRECT statement to reference A7. This would allow you to change only the sheet name in A5 but it would update the entire formula reference in whatever cell you had it in.

Excel – How to write an Excel formula that will paste a specific value in a different cell

The following approach makes use of a workaround described here to enable a worksheet function defined in VBA to set the value of another cell.

The custom function stores in global variables the address of the target cell and the value to which that cell is to be set. Then, a macro that is triggered when the worksheet recalculates reads the global variables and sets the target cell to the specified value.

Use of the custom function is straightforward:

  =SetCellValue(target_cell, value)

where target_cell is a string reference to a cell in the worksheet (e.g., "A1") or an expression that evaluates to such a reference. This includes an expression such as =B14 where the value of B14 is "A1". The function can be used in any valid expression.

SetCellValue returns 1 if the value is successfully written to the target cell, and 0 otherwise. Any previous contents of the target cell are overwritten.

Three pieces of code are needed:

the code defining SetCellValue itself
the macro that is triggered by the worksheet calculation event; and
a utility function IsCellAddress to ensure that target_cell is a valid cell address.

Code for SetCellValue Function

This code needs to be pasted into a standard module inserted into the workbook. The module can be inserted via the menu for the Visual Basic editor, which is accessed by selecting Visual Basic from the Developer tab of the ribbon.

  Option Explicit

  Public triggerIt As Boolean
  Public theTarget As String
  Public theValue As Variant

  Function SetCellValue(aCellAddress As String, aValue As Variant) As Long

      If (IsCellAddress(aCellAddress)) And _
             (Replace(Application.Caller.Address, "$", "") <> _
              Replace(UCase(aCellAddress), "$", "")) Then
          triggerIt = True
          theTarget = aCellAddress
          theValue = aValue
          SetCellValue = 1
      Else
          triggerIt = False
          SetCellValue = 0
      End If

  End Function

Worksheet_Calculate Macro Code

This code must be included in the code specific to the worksheet in which you will use SetCellValue. The easiest way to do this is to right-click the worksheet's tab in the Home view, select View Code, and then paste the code into the editor pane that comes up.

  Private Sub Worksheet_Calculate()

      If Not triggerIt Then
          Exit Sub
      End If
      triggerIt = False
      On Error GoTo CleanUp
      Application.EnableEvents = False
      Range(theTarget).Value = theValue
  CleanUp:
      Application.EnableEvents = True
      Application.Calculate

  End Sub

Code for IsCellAddress Function

This code can be pasted into the same module as the SetCellValue code.

  Function IsCellAddress(aValue As Variant) As Boolean

      IsCellAddress = False

      Dim rng As Range           ' Input is valid cell reference if it can be
      On Error GoTo GetOut       ' assigned to range variable
      Set rng = Range(aValue)
      On Error GoTo 0

      Dim colonPos As Long            'convert single cell "range" address to
      colonPos = InStr(aValue, ":")   'single cell reference ("A1:A1" -> "A1")
      If (colonPos <> 0) Then
          If (Left(aValue, colonPos - 1) = _
                Right(aValue, Len(aValue) - colonPos)) Then
              aValue = Left(aValue, colonPos - 1)
          End If
      End If

      If (rng.Rows.Count = 1) And _
          (rng.Columns.Count = 1) And _
          (InStr(aValue, "!") = 0) And _
          (InStr(aValue, ":") = 0) Then
          IsCellAddress = True
      End If                          'must be single cell address in this worksheet
      Exit Function

  GetOut:

  End Function