Forum Discussion
Excel formulas
If you want to enter a number in any "Income" cell, increment the adjacent "Quantity" cell and then clear the "Income" cell then you will need to use a macro (aka VBA code). Damien_Rosario was on the right track but you don't need the Developer tab to get to the VBA IDE (Integrated Developer Environment) - if you right click the sheet tab it gives you an option to:
Sheet Tab context drop-down
Which clicking View Code will take you to a window that looks like this:
Initial VBA IDE Worksheet pane Yours may not have
Option Explicit
If not, add it! It catches any typos in Variable names which could make your code useless.
Copy this code into the IDE Worksheet pane and read below for some explanation if interested.
Private Sub Worksheet_Change(ByVal Target As Range)
'Test if the changed (Target) cell is within your QUANTITY column
'If it isn't then the Intersection will be Nothing and you
'don't want to do anything to the sheet.
'If it is, then the Intersection will Not be Nothing so do things to sheet
If Not Intersect(Target, [Table1[QUANTITY]]) Is Nothing Then
'Add Target value to the value of the cell to its right
Target.Offset(0, 1) = Target.Offset(0, 1) + Target
'and clear the Target cell
Target.ClearContents
End If
End Sub
The lines starting with apostrophes are just comments which should show up in green in the code pane. They don't do anything and can be deleted if you want.
PS: Delete the merged row 2 if possible. Tables aren't designed to have merged cells - think of them more as list with fields (columns) that have names (headers) in the top row.