formulas for YTD

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

How do you retain a value already in a cell and have it
update with the value entered in an adjacent cell? Example
I do a lot of Year To Date worksheets. If I had 5
whatevers in Jan 04 in column A1 my YTD date column B1
would say 5. Now I SAVE AS (the workbook) and name it Feb
04 for the next month. I remove the 5 in Column A1 but
leave Column B1 alone. It will still say 5. When I get the
Feb 04 data and let's say it is 10, I want the YTD data in
Column B1 to update and say 15.

One way I know is to have the formulas refer back to the
previous (Jan 04 YTD column)but when you SAVE AS for the
next month the formulas has to be written again to refer
to the previous month. I do 100's of columns and was
wondering if there is a simpler way.........Thanks Bruce
..
 
Bruce,

You can use the worksheet's change event. Copy the code below, right click
on the sheet tab, select "View code", then paste the code into the window
that appears.

Note that if you ever need to change a value - say you entered 10 and meant
to enter 11 - then you need to undo your change by entering the negative
value of your mistake, or -10 in this example.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1").Value = Range("B1").Value + Target.Value
End If
End Sub
 
Thanks Bernie.............I was able to get this to work
in the first cell......now if it isn't to much trouble,
can you tell me how to copy it down the whole column and
will it work like copying any formula and reference the
cell adjacent to it? example if Iget it to work in a1 and
b1 can i just drag it down or copy it and it will work in
a2 and b2 etc?
 
Bruce,

To get it to work with multiple cells, you need to change the code. The
example changes below will work with range A1:A100. Note that as written,
only one cell at a time can be changed and have the running total updated.
If you want to change multiple cells (using ctrl-Enter) and have the running
total updated, simply delete the first IF statement.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Target(1, 2).Value = Target(1, 2).Value + Target.Value
End If
End Sub
 
Back
Top