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 expense
in one column and then the annual expenses in another column
For example, the January (month 1) expenses for line item 1001 ar
$100 and therefore the annual expenses for line item 1001 are $100
In February the 1001 expenses are $200 and therefore the annual 100
expenses are last month's 1001 expenses ($100) plus this month's 100
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
enter $200 into the February spreadsheet how can I add the annua
expenses to it without keeping a separate tally sheet somewhere else
Jim
Previous Posts In This Thread:
Adding a number to itself
I am making a budget spreadsheet in which I keep each month's expense
in one column and then the annual expenses in another column
For example, the January (month 1) expenses for line item 1001 ar
$100 and therefore the annual expenses for line item 1001 are $100
In February the 1001 expenses are $200 and therefore the annual 100
expenses are last month's 1001 expenses ($100) plus this month's 100
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
enter $200 into the February spreadsheet how can I add the annua
expenses to it without keeping a separate tally sheet somewhere else
Jim
Option ExplicitDim oldvalue As DoubleRight click sheet tab>view code>insert
Option Explici
Dim oldvalue As Doubl
Right click sheet tab>view code>insert this
Now, when you enter a value in a5 it will be added to what is ther
'===
Option Explici
Dim oldvalue As Doubl
Private Sub Worksheet_Change(ByVal target As Excel.Range
If target.Address = "$A$5" The
On Error GoTo fixi
Application.EnableEvents = Fals
If target.Value = 0 Then oldvalue =
target.Value = 1 * target.Value + oldvalu
oldvalue = target.Valu
fixit
Application.EnableEvents = Tru
End I
End Su
-
Don Guillet
Microsoft MVP Exce
SalesAid Softwar
(e-mail address removed)
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 wa
not done correctly
Excel sheet has many cells
Why not use them
Gord Dibben MS Excel MV
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 cel
trail) is a Good Thing? so I made a matrix sheet of account number
and months, then linked the main sheet to each cell in the matri
sheet so that I have a running tally on the main sheet of that month
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:
Good thinnin'Gordwrote:
Good thinnin'
Gord
wrote:
Agreed.
Agreed. I was just trying to accommodate OP
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
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/tutorial...c-b0877c10ecb4/how-to-display-a-gravatar.aspx