I'm trying to add a formula to a specific cell that says "Enter user data then add 20" Ex: If a user enters 10 into a cell when they press enter the cell returns 30.

I've tried a formula that says, =this cell (plus this cell plus 20) but can't have a formula and user input in the same cell.

  • have a look at a similar problem asked earlier on SO, this should solve your problem, you will need to use VBA - resolved-error.com/questions/21862981/… – Bharat Anand Apr 15 at 23:38
  • Formula cannot change the value of the cell where the data is entered. In order to change the value of the cell in the same cell where it is entered VBA as previously noted is required – Forward Ed Apr 16 at 0:04
  • Conceptually, data entry and data processing should be separate, otherwise you don't have any visibility over what processing has been completed. Manipulating the data that the user has entered, with VBA to overwrite that data, you lose the audit trail. What's wrong with a formula in the next cell that calculates the desired result? That is how it should be done. – teylyn Apr 16 at 0:46

Create a named range on the cell that you are wanting to add 10 to, I called that range rngCellToAdd10To ...

enter image description here

Then add the below code (using the VBA editor) to the worksheet object that the cell you want to monitor changes for ...

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next

    If Intersect(Target, Range("rngCellToAdd10To")) Then
        Application.EnableEvents = False
        Range("rngCellToAdd10To").Value = Range("rngCellToAdd10To").Value + 10
        Application.EnableEvents = True
    End If
End Sub

enter image description here

... then add a number to the cell and watch it go.

enter image description here

If you're not sure how to add code into the VBA editor, I suggest Googling it.

  • Thank you! That was fun, I got it to work. But I actually need it to add the value of a cell. I thought asking for "10" in the question would make things easier and I could just sub it out but I was wrong! Is there a way I can add "Cell F7" instead of 10? – Max Apr 16 at 15:30
  • @Max yes, that’s straight forward. You literally just follow the same steps by creating a named range (it’s safer than specifying a specific cell) on the cell in question and then replace the “10” in the code with Range(“rngAdditionalValue”).Value ,.. if that’s the name of your range. Make sense? – Skin Apr 16 at 20:28
  • It does that worked. Thanks! – Max Apr 16 at 22:53
  • @Max congratulations, you’re officially a VBA programmer. – Skin Apr 16 at 22:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.