Excel - Change the value of a cell from another cell, but changed cell keeps value when input cell is cleared

Question

I'm basically trying to make an input cell. What I want is when I change the value of C2, it changes the value of C1. But when I erase C2 I want C1 to keep the value.

So if I make C2 = "Adam", C1 = "Adam", but then I change C2 = "" and C1 is still "Adam".

Can anyone help me out with this? I've been looking online for ages and can't find anything to help me find a solution.

Answer 1

You can do this on the Worksheet_Change event,

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 2 And Target.Column = 3 Then    'C2
        If Target.Value <> "" Then
            Range("C1").Value = Target.Value
        End If
    End If
End Sub

A formula =C2, or some variation, won't work because it cannot remember a value that has since been deleted.

---

I suppose you could kind-of fake a formula like the following, but I'm definitely not recommending it, especially in combination with code. I'm just posting it out of interest.

Private Sub Worksheet_Change(ByVal Target As Range)
    Static old As String

    If Target.Row = 2 And Target.Column = 3 Then    'C2
        If Target.Value <> "" Then
            Range("C1").Formula = "=IF(N(""""),"""","""")&C2"
            old = Target.Value
        Else
            Range("C1").Formula = "=IF(N(""" & old & """),"""","""")& """ & old & """"
        End If

    End If
End Sub

It is pointless because you still need code anyway.

---

I suppose it is even possible for the code to just write to a named range/value that the formula then refers to:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value <> "" Then
        ActiveWorkbook.Names.Add Name:="stored", RefersToR1C1:="=""" & Target.Value & """"
    End If
End Sub