Adding a number to itself

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

Option Explicit
Dim oldvalue As Double
Right click sheet tabview codeinsert 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

"RST Engineering" wrote in message
...
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

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
wrote:

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

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:

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
wrote:

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

Good thinnin'

Gord

On Tue, 22 Dec 2009 16:34:41 -0800, RST Engineering
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
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:

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
wrote:

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

Agreed. I was just trying to accommodate OP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gord Dibben" gorddibbATshawDOTca wrote in message
...
Good thinnin'

Gord

On Tue, 22 Dec 2009 16:34:41 -0800, RST Engineering
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
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:

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
wrote:

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

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 itg

Merry Xmas


Gord

On Wed, 23 Dec 2009 17:02:49 -0600, "Don Guillett"
wrote:

Agreed. I was just trying to accommodate OP

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

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

Previous Posts In This Thread:

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

Adding a number to itself
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

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

Option ExplicitDim oldvalue As DoubleRight click sheet tabview codeinsert
Option Explicit
Dim oldvalue As Double
Right click sheet tabview codeinsert 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


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

"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 itg

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...-gravatar.aspx