Year To Date Formula

Smurfit-Stone

Active Member
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

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.

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

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

Thanks.......

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

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

here is some code that would hide the columns for any month that is less than or equal to the date in cell A1.
Code:
``````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``````

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

Replies
2
Views
321
Replies
5
Views
186
Replies
24
Views
646
Replies
0
Views
210
Replies
5
Views
241

Threads
1,207,097
Messages
6,076,556
Members
446,213
Latest member
bettigb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back