Adding up within a cell during data input
 

Hi, I wonder if anyone could help with a query I have around data input.

I'm working on spreadsheets recording stats we receive daily, calculating
the totals on a calculator and then overwriting the existing number. For
example, if the cell shows '200' and our daily feed brings out an increase of
45, I am manually entering '245'.

I know it is possible to enter the data by formula, so instead of overtyping
'245' I have been entering '=200+45'. What I would like to know is if there
is a way of simply entering '45' into the cell and have excel automatically
add it to what number is already there. Is it possible to do this?

Thanks in advance for any advice!

Adding up within a cell during data input
 

This example uses cell B9 as an accumulator.

Put the following code in a standard module

Public buildup As Variant
Sub StartMe()
buildup = Range("B9").Value
Application.EnableEvents = True
End Sub

Next put the following code in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set b9 = Range("B9")
Set t = Target
If Intersect(t, b9) Is Nothing Then Exit Sub
Application.EnableEvents = False
b9.Value = b9.Value + buildup
buildup = b9.Value
Application.EnableEvents = True
End Sub

Then run StartMe.
Then start typing values in cell B9.
--
Gary''s Student - gsnu200810


"purelycreativeadam" wrote:

Hi, I wonder if anyone could help with a query I have around data input.

I'm working on spreadsheets recording stats we receive daily, calculating
the totals on a calculator and then overwriting the existing number. For
example, if the cell shows '200' and our daily feed brings out an increase of
45, I am manually entering '245'.

I know it is possible to enter the data by formula, so instead of overtyping
'245' I have been entering '=200+45'. What I would like to know is if there
is a way of simply entering '45' into the cell and have excel automatically
add it to what number is already there. Is it possible to do this?

Thanks in advance for any advice!

Adding up within a cell during data input
 

Hi Gary

Thanks for the help. As Im unfamiliar with the code editor, I may need to
ask some further questions - do you know if it is possible to apply this to a
range of cells?

"Gary''s Student" wrote:

This example uses cell B9 as an accumulator.

Put the following code in a standard module

Public buildup As Variant
Sub StartMe()
buildup = Range("B9").Value
Application.EnableEvents = True
End Sub

Next put the following code in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set b9 = Range("B9")
Set t = Target
If Intersect(t, b9) Is Nothing Then Exit Sub
Application.EnableEvents = False
b9.Value = b9.Value + buildup
buildup = b9.Value
Application.EnableEvents = True
End Sub

Then run StartMe.
Then start typing values in cell B9.
--
Gary''s Student - gsnu200810


"purelycreativeadam" wrote:

Hi, I wonder if anyone could help with a query I have around data input.

I'm working on spreadsheets recording stats we receive daily, calculating
the totals on a calculator and then overwriting the existing number. For
example, if the cell shows '200' and our daily feed brings out an increase of
45, I am manually entering '245'.

I know it is possible to enter the data by formula, so instead of overtyping
'245' I have been entering '=200+45'. What I would like to know is if there
is a way of simply entering '45' into the cell and have excel automatically
add it to what number is already there. Is it possible to do this?

Thanks in advance for any advice!

Adding up within a cell during data input
 

Yes - But the code would need some modifications.
--
Gary''s Student - gsnu200810


"purelycreativeadam" wrote:

Hi Gary

Thanks for the help. As Im unfamiliar with the code editor, I may need to
ask some further questions - do you know if it is possible to apply this to a
range of cells?

"Gary''s Student" wrote:

This example uses cell B9 as an accumulator.

Put the following code in a standard module

Public buildup As Variant
Sub StartMe()
buildup = Range("B9").Value
Application.EnableEvents = True
End Sub

Next put the following code in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set b9 = Range("B9")
Set t = Target
If Intersect(t, b9) Is Nothing Then Exit Sub
Application.EnableEvents = False
b9.Value = b9.Value + buildup
buildup = b9.Value
Application.EnableEvents = True
End Sub

Then run StartMe.
Then start typing values in cell B9.
--
Gary''s Student - gsnu200810


"purelycreativeadam" wrote:

Hi, I wonder if anyone could help with a query I have around data input.

I'm working on spreadsheets recording stats we receive daily, calculating
the totals on a calculator and then overwriting the existing number. For
example, if the cell shows '200' and our daily feed brings out an increase of
45, I am manually entering '245'.

I know it is possible to enter the data by formula, so instead of overtyping
'245' I have been entering '=200+45'. What I would like to know is if there
is a way of simply entering '45' into the cell and have excel automatically
add it to what number is already there. Is it possible to do this?

Thanks in advance for any advice!