Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Adding a number to itself

4 views
Skip to first unread message

RST Engineering

unread,
Dec 22, 2009, 3:17:43 PM12/22/09
to
I am making a budget spreadsheet in which I keep each month's expenses
in one column and then the annual expenses in another column.

For example, the January (month 1) expenses for line item 1001 are
$100 and therefore the annual expenses for line item 1001 are $100.

In February the 1001 expenses are $200 and therefore the annual 1001
expenses are last month's 1001 expenses ($100) plus this month's 1001
expenses ($200) which is $300.

Without keeping a separate column for each month's line item expenses,
is there a tricky way of adding a number to itself? That is, when I
enter $200 into the February spreadsheet how can I add the annual
expenses to it without keeping a separate tally sheet somewhere else?

Jim

Don Guillett

unread,
Dec 22, 2009, 4:44:41 PM12/22/09
to
Option Explicit
Dim oldvalue As Double
Right click sheet tab>view code>insert this.
Now, when you enter a value in a5 it will be added to what is there
'====
Option Explicit
Dim oldvalue As Double
Private Sub Worksheet_Change(ByVal target As Excel.Range)
If target.Address = "$A$5" Then
On Error GoTo fixit
Application.EnableEvents = False
If target.Value = 0 Then oldvalue = 0
target.Value = 1 * target.Value + oldvalue
oldvalue = target.Value
fixit:
Application.EnableEvents = True
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com
"RST Engineering" <jwe...@gmail.com> wrote in message
news:e0a2j5dkv8d2cs5sj...@4ax.com...

Gord Dibben

unread,
Dec 22, 2009, 5:11:46 PM12/22/09
to
Can be done.....see Don's response.

But what you are asking for is fraught with peril.

No way to trouble shoot any errors in data input.

Once the new value is added to old, the old data is gone.

Say you had 100 in a cell, then you want to add 200.

Don's code will allow that but what if you inadvertantly added 209?

How will you keep track of current and past entries?

You have no 'paper trail" to follow to see how you got the results you have.

You'll be scratching your head wondering where and when the data input was
not done correctly.

Excel sheet has many cells.

Why not use them?


Gord Dibben MS Excel MVP

On Tue, 22 Dec 2009 12:17:43 -0800, RST Engineering <jwe...@gmail.com>
wrote:

RST Engineering

unread,
Dec 22, 2009, 7:34:41 PM12/22/09
to
I came to that conclusion myself, the "paper" trail (actually a cell
trail) is a Good Thing� so I made a matrix sheet of account numbers
and months, then linked the main sheet to each cell in the matrix
sheet so that I have a running tally on the main sheet of that months
expenses plus a summed column of the year to date expenses.

To change months you do a simple Find&Replace replacing last month's
column letter with this month's column letter. Pretty simple once I
got my head wrapped around it.

Thanks for all your help...

Jim


On Tue, 22 Dec 2009 14:11:46 -0800, Gord Dibben <gorddibbATshawDOTca>
wrote:

Gord Dibben

unread,
Dec 23, 2009, 5:00:51 PM12/23/09
to
Good thinnin'

Gord

On Tue, 22 Dec 2009 16:34:41 -0800, RST Engineering <jwe...@gmail.com>

Don Guillett

unread,
Dec 23, 2009, 6:02:49 PM12/23/09
to
Agreed. I was just trying to accommodate OP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:tcp4j59dn236t8mia...@4ax.com...


> Good thinnin'
>
> Gord
>
> On Tue, 22 Dec 2009 16:34:41 -0800, RST Engineering <jwe...@gmail.com>
> wrote:
>
>>I came to that conclusion myself, the "paper" trail (actually a cell

>>trail) is a Good Thing� so I made a matrix sheet of account numbers

Gord Dibben

unread,
Dec 23, 2009, 8:03:24 PM12/23/09
to
I find myself doing that Don.

Sometimes we give OP's only what they ask for.

The old adage..........be careful what you ask for, you just might get it<g>

Merry Xmas


Gord

judyramirez

unread,
Mar 1, 2010, 4:46:44 PM3/1/10
to
Let's say that the your Y-T-D is column D1 and your monthly column is called C1, then what you want to do is in column D1 enter the formula: =SUM(C1,D1). You are going to get an error message (called a circular reference)close it. Now go to the excel menu (it's the little circle on the left hand corner with the microsoft symbol) and click on excel options, then go to formulas.
There you will enable iterative calculation and enter 1 in the maximum iterations and in the maximum change also.

i hope this helps...

RST Engineering wrote:

Adding a number to itself
22-Dec-09

Jim

Previous Posts In This Thread:

On Tuesday, December 22, 2009 3:17 PM
RST Engineering wrote:

Adding a number to itself

Jim

On Tuesday, December 22, 2009 4:44 PM
Don Guillett wrote:

Option ExplicitDim oldvalue As DoubleRight click sheet tab>view code>insert


Option Explicit
Dim oldvalue As Double
Right click sheet tab>view code>insert this.
Now, when you enter a value in a5 it will be added to what is there
'====
Option Explicit
Dim oldvalue As Double
Private Sub Worksheet_Change(ByVal target As Excel.Range)
If target.Address = "$A$5" Then
On Error GoTo fixit
Application.EnableEvents = False
If target.Value = 0 Then oldvalue = 0
target.Value = 1 * target.Value + oldvalue
oldvalue = target.Value
fixit:
Application.EnableEvents = True
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com

On Tuesday, December 22, 2009 5:11 PM
Gord Dibben wrote:

Can be done.....see Don's response.
Can be done.....see Don's response.

But what you are asking for is fraught with peril.

No way to trouble shoot any errors in data input.

Once the new value is added to old, the old data is gone.

Say you had 100 in a cell, then you want to add 200.

Don's code will allow that but what if you inadvertantly added 209?

How will you keep track of current and past entries?

You have no 'paper trail" to follow to see how you got the results you have.

You'll be scratching your head wondering where and when the data input was
not done correctly.

Excel sheet has many cells.

Why not use them?


Gord Dibben MS Excel MVP

wrote:

On Tuesday, December 22, 2009 7:34 PM
RST Engineering wrote:

I came to that conclusion myself, the "paper" trail (actually a celltrail) is
I came to that conclusion myself, the "paper" trail (actually a cell

trail) is a Good Thing? so I made a matrix sheet of account numbers


and months, then linked the main sheet to each cell in the matrix
sheet so that I have a running tally on the main sheet of that months
expenses plus a summed column of the year to date expenses.

To change months you do a simple Find&Replace replacing last month's
column letter with this month's column letter. Pretty simple once I
got my head wrapped around it.

Thanks for all your help...

Jim


wrote:

On Wednesday, December 23, 2009 5:00 PM
Gord Dibben wrote:

Good thinnin'Gordwrote:
Good thinnin'

Gord

wrote:

On Wednesday, December 23, 2009 6:02 PM
Don Guillett wrote:

Agreed.


Agreed. I was just trying to accommodate OP

--


Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com
"Gord Dibben" <gorddibbATshawDOTca> wrote in message

On Wednesday, December 23, 2009 8:03 PM
Gord Dibben wrote:

I find myself doing that Don.Sometimes we give OP's only what they ask for.


I find myself doing that Don.

Sometimes we give OP's only what they ask for.

The old adage..........be careful what you ask for, you just might get it<g>

Merry Xmas


Gord


Submitted via EggHeadCafe - Software Developer Portal of Choice
How to display a Gravatar Image with 100 Percent Client Script Code
http://www.eggheadcafe.com/tutorials/aspnet/3c8a04cd-471e-48b7-8dcc-b0877c10ecb4/how-to-display-a-gravatar.aspx

0 new messages