Answers to sum in same cell

[Hero]

Hi

Can I have some formula help please

If cell E2 says L then if in box G2 I type in the number 245 I want Excel to calculate 245-30 and type the answer 215 into cell G2 when I press the ENTER button on the keyboard.

But

If cell E2 says S I want Excel to calculate 245+30 and type the answer 275 into cell G2 when I press the ENTER button on the keyboard.


Thank you

[martindwilson]

this cannot be done with functions

[Paul]

Hi Hero,

You would need to use VBA for this, as a cell cannot maintain a formula and a static value simultaneously. Once you type a number into G2 it will overwrite the formula.

Right-click on the sheet tab you're using and choose View Code. Paste the following code into the VB Editor window (right side, white space) then close the VB Editor window. Whenever you change cell G2 the macro will be triggered. (Technically it will be triggered on any change, but if it doesn't involve a change to G2 it will skip the procedure.)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("G2")) Is Nothing Then
    If Range("E2").Value = "L" Then
        Range("G2").Value = Range("G2").Value - 30
    ElseIf Range("E2").Value = "S" Then
        Range("G2").Value = Range("G2").Value + 30
    Else
        ' Do nothing, keep G2 as whatever value you entered.
    End If
End If
Application.EnableEvents = True
End Sub

[Hero]

Thanks for this. I had a try and could not find View Code when I right clicked. I'm afraid this is too complex for me. I'll stick to easier methods.

[Paul]

If you right-click on any sheet tab you should see View Code, unless someone has removed that entry from the list using a different macro.

Regardless, you can also press ALT+F11 to open the VB Editor, or open it through the Tools menu (Excel 2003). When opening it using either of these methods, just look at the left hand side once it's open and double-click on the sheet for which you want the code to run, then paste my code into the window (empty white space, likely) to the right of that.

[Hero]

Hi Paul,

That worked. Great.

I tried to drag G2 in Excel so that the formula you told me to past could be moved around to other cells but that didn't work. Is there a way of doing that?