Is it possible to enter a value in a cell and have a formula immediately replace it with the result of the formula?

[FrumpyJones]

Hi Everyone,

I tried googling and searching this forum for this question. And either I'm not asking/searching it correctly, or it's simply an "undoable" thing and everyone knows but me.

I want to put a number in say, C1. When I hit enter on that, I want a formula to run that will then convert that number to the result.

I know I can put a formula in D1 that can give me the result C1 + Formula, but is there anyway to have it do it without having to make another visible column?

Example: I want any number put into C1 to be multiplied by 2, and return that value to C1 instead of the number they put in. So:

Put 3 into C1, hit enter (or tab).
3*2 = 6
Replace the 3 in C1 with 6 via magic.​

Is this possible without going all Visual Basic on it?

Thanks for the help

 

[Joe4]

A cell can only contain:
- a hard-coded value
- a formula
Its one or the other; it is impossible to have both at the same time.

What you want to do requires VBA, specifically there is code that will run automatically upon some cell being updated.
It is actually pretty straightforward, as far as VBA code goes (nothing too complex).

 

[Joe4]

The VBA code would look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    
    If Target.Address = "$C$1" Then
        If IsNumeric(Target.Value) Then
            Application.EnableEvents = False
            Target = Target.Value * 2
            Application.EnableEvents = True
        End If
    End If
    
End Sub

All you need to do is right-click on the sheet tab name at the bottom of your worksheet, select "View Code", and paste the code above in the resulting VB Editor window. That's it. The code will run automatically by itself, as long as you have VBA/Macros enabled.

 

[still learning]

Hi
Joe4, what about an if statement the returns blank if there is no value in it

=IF(C1="","",+C1*2)

mike (still learning using this site and i have used your help, thank you)

 

[Joe4]

Joe4, what about an if statement the returns blank if there is no value in it

No good. You cannot put that formula in cell C1. That would only work if you place it in another cell.
Note the original question:

I want any number put into C1 to be multiplied by 2, and return that value to C1 instead of the number they put in.

 

[FrumpyJones]

Thank you all so much for the replies. I was feeling this was a VB answer. The end hope was to share this to a very large team, and I shudder at the thought of having to go through the upteen calls/e-mails of "it's not working for me", "how do I enable it? Is it safe to enable it.." etc...

So an extra column with a formula it is!