Special Addition/Subtraction Function

  • Thread starter Thread starter Annabelle
  • Start date Start date
A

Annabelle

Problem: I have a spreadsheet that I'm updating from
various sources, so I must manually add or subtract
numbers in a specific cell. This is time consuming and
puts accuracy at risk. To address this problem, I would
like cell J9 (running total) to accept additions or
subtractions (these are numbers representing hours) from
numbers entered into cell K9.

Example: J9 has the number 225. I need to add 20 hours, so
I enter 20 in cell K9. The expectation is that J9 now
reads as 245.

Obvious problems include: what happens to J9 when I clear
K9 or enter a new number? Again, I would expect the new
number I enter to add to the number in J9 (or subtract, if
entered as negative).
 
I would really advice against doing this since you don't have any
possibility to audit it,
it would be much better to use more cells from input and total all the cell
instead of one.
If you insist, do tools>options>calculations and check iteration and set it
to 1, then in J9 put this
formula

=J9+K9


Also if you select J9 and the sheet updates it will keep adding what's in K9
so to give you at least some chance of overview use multiple cells so you
can track the total
 
Try writing this in the Worksheet_Change section in VBA:

If Target.Address = "$K$9" Then
Range("J9") = Range("J9") + Target.Value
End If
Type in a value in "K9" and you will have a running sum.

If you cannot do any programming, then I have a messy solution for you.
It works, if you can live with this.

You are trying to do what Excel calls circular references. You are
asking a cell to use its own value, add something to it then refresh
itself. Excel warns us of this with the "Circular reference" comment.
However you can make Excel deal with it.

In the menu "Tools", "Options", "Calculation", select "Manual",
"Iteration", and "Max Iterations=1".

To get started, write in 225 in "K9", and the formula "=J9+K9" in "J9".
Press "F9" to make Excel calculate the worksheet. Write in 20 in "K9"
and press "F9" again to make the running sum work. Once you are
fininished, clear "K9" and go back to "Automatic" calculation via the
menu.
 
This worked, thank you for your help and advise. I will
look at alternatives for critical data points.
 
Back
Top