Formula to derive "Year-To_Date"

[Vince]

I have a column designated as "Year-To-Date", and like to enter a formula so
that every time I enter a value in a cell in the same row (for instance B2),
it can then add that value to the current value in the Year-To-Date cell (for
instance D2) and give me a total Year-To_Date?? Any help appreciated.

 

[Gary''s Student]

Try this small worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim D2 As Range, B2 As Range
Set B2 = Range("B2")
Set t = Target
Set D2 = Range("D2")
If Intersect(B2, t) Is Nothing Then Exit Sub
Application.EnableEvents = False
D2.Value = D2.Value + B2.Value
B2.Clear
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Vince]

I tried it on one cell and worked fine, thanks. I do need to however
continue this fomat all the way down the page for about 15 rows, since I have
different numbers in the cells that I need to get the Year-To-Date for. How
would I modify this Macro so that it applies to more than one cell. Thanks

 

[Frank]

Hi Vince,

You could try using a formula in column D.

=IF(B2="","",D1+B2)

Francis J Hayes (The Excel Addict)
http://www.TheExcelAddict.com

 

[Shane Devenshire]

Hi,

Here is another approach, suppose your entry is in cell B1 then the formula
in D1 for YTD would be

=IF(B1<>"",SUM(B$1:B1))

Copy this formula down as far as need. Note the $, it is critical.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Gary''s Student]

Hi Vince:

Here is a new version. It must replace the old version.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim B As Range
Set B = Range("B1:B100")
Set t = Target
If Intersect(B, t) Is Nothing Then Exit Sub
Application.EnableEvents = False
With t.Offset(0, 1)
.Value = .Value + t.Value
t.Clear
End With
Application.EnableEvents = True
End Sub

You must also pdate the statement:

Set B = Range("B1:B100")

to suite your needs.

 

[Vince]

Hi,

I'm almost there, just need a few small modifications:
1. YTD Column is E and starts from E2 (My error, sorry) and the data will
still come from Column B (starting from B2 and down).
2. Since I make a new Monthly Statement every month, I have a template that
fill up and resave for every month. Therefore the next month I re-open the
template and fill up column B with new values BUT column E (Year-To-Date)
needs to keep the previous month's value/data and get updated automatically
with the current month's value (COLUMN B cells) added to the total figure in
the specific cell (COLUMN E cells). ANY HELP TO MAKE THIS FUNCTIONAL IS
GREATLY APPRECIATED.