How do you write an Excel formula that will paste a specific value in a different cell?

So basically I want to write a formula that will check a condition, and if that condition is met, then I want to paste a specific line of text in a different cell. I should note that I do not want the formula to exist in the cell I want to paste a value into. So for example, if I want to paste a value into B5, I don't want the formula to be in cell B5...

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

Let's assume you want the text "Text A' to show in cell C5 if the cell B5 contains the value "green".

You can use a formula approach, but since formulas cannot change values in other cells, the formula will need to be entered into cell C5.

=IF(ISNUMBER(FIND("green",B5)),"Text A","")

Cell C5 will now only show "Text A" if B5 contains the word "green".

A formula like this can be built to work with many conditions. You need to define your requirements in order to get help on your specific situation.

If you don't want C5 to have a formula, you can also use a VBA approach. You can run a Worksheet Change event that will run whenever cell B5 is changed, either by manually editing the value or by pasting something into it.

An example for such a macro could be

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
    If InStr(1, Target, "green", vbTextCompare) Then
        Target.Offset(0, 1) = "Text A"
    End If
End If
End Sub

The conditions and the placement of the output are just an example, of course and need to be adjusted to your requirements.

The difference between the formula and the macro approach is

• with the formula approach, cell C5 will contain a formula. If the user accidentally deletes the formula, the functionality it delivers will be deleted, too. (There are ways to manage that, though)
• with the VBA macro, cell C5 will not show any formula, and will have the verbatim text as a value, but changing the setup requires knowledge of Excel VBA. Also, with a VBA approach, the workbook must be saved as a macro-enabled workbook and the user must allow macros or make the file a trusted file.

Note: the above is just an example. You need to define your requirements, whether to evaluate numbers or text, whether evaluation is case sensitive, what the evaluation rules are, where to place the result, etc.

To check if a condition is met, write an IF formula in some cell to update "a specific line of text in a different cell". The result cell will only contain the value of the formula, NOT the formula that generated that value, like so:

Working cell (eg. J5) =IF(A1="yes","Specific line of text","")
Result cell (eg. B5) =J5

So if the condition is met (A1="yes"), B5 will contain "Specific line of text". Otherwise it stays blank.

Notes:
Cells that have variable values will normally contain some kind of formula to keep updating it's values.

If you don't want anyone to know the real formula that's creating the value, the formula can go in some other cell to generate the value, which will then be copied to the result cell eg. B5.

To hide formula that generated the value or to hide the cell that B5 refers to (to keep checking if value needs updating), refer to:
https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f

Other functions that can achieve the same effect includes CHOOSE, HLOOKUP, LOOKUP, VLOOKUP.