Year To Date Formula

[Smurfit-Stone]

Hello Board,

I have a spreadsheet where columns B-F is the current month totals, columns G-K are Year to date totals. I have 107 rows. How can I format a formula to keep adding YTD totals in columns G-K even when I refresh columns B-F for the next months figures? I hope that made sense.....Thanks in advance

 

[texasalynn]

so if you already have those columns and then you add in data, why doesn't your YTD formulas update? Maybe you need to post some sample data using Colo's utility found at the bottom of this page.

 

[Smurfit-Stone]

My formulas is a simple Sum(), I need to be able to add new data to the column for the monthly figures. If I have say 50,000 in 2006 YTD Sales(col. G) and in for July I entered 10,000, the new YTD is 60,000. I want to now clear or type over the 10,000 and add August figure of 8,000 and have my YTD column reflect the new total of 68,000. The sum() formula is working, I just thought there might be something better. Thanks

 

[texasalynn]

u r right - that can't be done. Not unless you have some code that would take current value and add new amount to it. A formula is not going to help you

 

[Smurfit-Stone]

Thanks.......

 

[texasalynn]

so why don't you make a column for each month then you can add data and have the YTD work

 

[Smurfit-Stone]

Good Idea, and I could hide the previous and future months.....your so cool.....Thanks

 

[texasalynn]

here is some code that would hide the columns for any month that is less than or equal to the date in cell A1.

Sub CurrentData()
'To hide columns month is <= actual date)
LC = Cells.SpecialCells(xlCellTypeLastCell).Column
Sdate = Range("A1").Value
For i = 1 To LC
    If Month(Cells(3, i)) <= Month(Sdate) Then
        Columns(i).Hidden = True
        Else
        'leave visible
    End If
Next i

End Sub

 

[Smurfit-Stone]

Things are BIGGER in Texas.......thanks again. :wink: