Help in creating generic month + previous month formula to calculate YTD

[SoBadWithExcel]

Hello, everyone,

I must start with the fact that I have almost a kindergarten-level understanding of Excel. I have been tasked with creating a 12-sheet (monthly) template for keeping stats in several categories.

So far, my attempts to find help have been fruitless as I either find info that doesn't fit what I need OR I find something quite related but it really jumps quickly into language/understanding that I simply don't have.

So in my example, let's say monthly values are in column C and YTD is in column D. So if I am wanting July's YTD in row 3, I have been using this formula, which I got somewhere and does seem to work: =Jun!D3+Jul!C3

This formula gets me the right numbers month-on-month.

Where I get stuck is that I am having to do this manually for every YTD value for each new month. I find it impossible to believe that there does not exist a formula that includes within itself all previous months so that the same one can be used page after page. Is there a way to just say (in Excel-ese): SUMGenericPreviousMonthYTD+GenericCurrentMonthMonthly?

Please tell me this makes sense! As it is now, I have over 6 months' worth of manually inputting the formulas for each new month on each new sheet. Please tell me there is a way to do this where I can do it once and just copy sheets that will automatically add previous month without having to name it.

Thanks in advance!

 

[Bosquedeguate]

What is the naming convention for your monthly worksheets? "Jun2023" or something like this?

 

[SoBadWithExcel]

Just the first three letters of the month, as I don’t need to do any crossover between years. Jan, Feb, etc.

 

[Bosquedeguate]

Just the first three letters of the month, as I don’t need to do any crossover between years. Jan, Feb, etc.

Perfect!

 

[Bosquedeguate]

Will something like this work for you? Do you have a date on each tab? One is needed (could it be added)?
If there is a date what cell is it in?

The YTD formula creates a reference to the previously named month (sheet assuming a 3-letter sheet name like: Jan, Feb, Mar, ...) and Cell D3 on that tab (sheet).
The dynamic reference for the Aug YTD value would be "Jul!$D$3". And the value of cell C3 is added to it.

Please let me know if this needs to be tweaked.

PrevMoYTDCalc.xlsx
	A	B	C	D
1	Month
2	Aug-23		Month Bal	YTD Bal
3			888	3996
Aug

Cell Formulas
Range	Formula
D3	D3	=INDIRECT(TEXT($A$2-1,"mmm") & "!$D$3") + C3