I am having a heck of a time building a formula in the title. I'm losing it trying to keep it straight in my head.
The sheets: December 2020, January 2021, February 2021, etc... (months go back for years, ill only be applying this formula to future sheets) and a sheet called Calculations (this is for static data)
Every sheet is copied from the prior month's sheet and the data is zeroed out manually. It is essentially a budget sheet. They contain: A1 contains a dropdown for the month to select the month this sheet tracks. A2 contains the year. G1 contains the data which I wish to use in the calculations (Say, 4402.11 formatted as currency.)
There are a few things I would like to do which I am sure that I could figure out on my own if I was given a nudge.
First calculation: If the current sheet is April 2021. I want to automatically populate a cell with data from the immediate prior month's sheet 'March 2021'!G1. Populate another cell with the data from 2 months prior, etc...
I was trying to use =address() and =indirect() to try to figure this out. Calculation has A1 = January, A2 = February, etc.. so I was going to use a function to compare the month sheet's month to that array to find the cell, convert the month to a number with Address, add 1, then reverse the process to get the previous month, etc.... If I can get these cells, I can build the average and other items from there.
Ive found a lot of other stack questions and answers which is what have given me bits and pieces of my issue, but it feels like I am going about this wrong and in a completely inefficient way.
I could change my "make a new month copy" procedure from 2 steps to 5 steps by having 2+x cell changes where X is how many prior month's to pull. Example: A1 + A2 is current, I would use A3 + A4 for first prior month, A5 + A6 for 3rd prior month, but I am trying to avoid that and make it automatic. Its already enough work to zero the real meat of the sheet as this sheet has been 4 years in the making of tweaks here and there.
The end goal is to create cells that show average and trends based on current and next 3 older sheets for quick viewing. Currently if this data is needed it is done freehand by tabbing through the sheets or pulling reports from the datasources this document is filled from (meaning thats never done so we "guess" on if the numbers are trending up or down)
Edit1: Here is a sheet which illustrates what I am trying to do. Please read the General Notes / Instructions sheet https://docs.google.com/spreadsheets/d/1LFXRM-Np714yAtmefPsslMK0JqwVQF449dFMKNGcLds/edit?usp=sharing
