0

I am trying to write a formula make excel place data in a cell based on the contents of a different cell BUT the formula cannot be in the cell where excel places the data.

If we take the below simple formula and place it in Cell B1 it works fine: =IF(A1=1,"Okay","Nope")

If I type the number 1 in cell A1 then excel returns Okay in Cell B1 and Nope if A1 is empty or has a different number.

But that is not what I want

I want something like If A1 contains the number 1 then Excel should place/populate cell C1 with the word "Okay" =IF(A1=1,C1="Okay","")

To be clear this is not updating the cell that contains to formula depending on the contents of a different cell, this is about placing specific text in a different cell based on another cells contents without the formula being in the cell where the text is placed.

It seems such a simple task to me but I am unable to work out how to make it happen.

Is there something I am missing?

Kenny
  • 49

2 Answers2

1

This can be done with a VBA macro:

Sub PutData()
    If Range("A1").Value = 1 Then
        Range("C1").Value = "Okay"
    Else
        Range("C1").Value = "Nope"
    End If
End Sub

First type a value in cell A1 and then run the macro.

If required:

  • the macro can be modified to handle multiple cells
  • the execution of the macro can be made automatic, so just changing A1 would cause the macro to run.
Gary's Student
  • 19,636
0

Since you don't want any Formula for the issue then MACRO (VBA code) is the best solution. And to automate the entire process I would like to recommended Worksheet_change event rather than simple MACRO.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target.value = 1 Then
        Range("C1").value = "Okay"
     ElseIf Target.Address = "$A$1" And Target.value <> 1 Then
     Range("C1").value = "Nope"
    End If
End Sub

How it works:

  • Copy & Paste this code as standard Module.
  • As soon you populate Cell A1 with 1 the Macro will put Okay in Cell C1, if Cell A1 is blank or has any value other that 1 then then Macro will put Nope in cell C1.

Note:

  • Adjust cell references in the code as needed and you may replace Nope with Blank or any other value also.
Rajesh Sinha
  • 9,403