ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Sample sheet for problem listed: https://stackoverflow.com/questions/67274248/google-sheets-rolling-average-formula-pulling-data-from-multiple-sheets
2
3
Sheet labeled Calculations houses all static data / calculations / constants that are used elsewhere in the book. Currently has A1-12 listed a months.
4
5
Sheets labeled "(Month) YYYY" are duplicated from the prior month and are more-or-less the same. They are not copied from a template because tweaks throughout the years are cumulative and I would rather not maintain a template
6
November 2020 - February 2021 are dummy sheets just so data can be referenced as a source (cells A2, B2, and B10 in each sheet)
7
cell A2 is using Data Validation to populate a drop down with the months listed in Calculations!A1-A12
8
cell B2 is YYYY keyed manually
9
10
11
March 2021 is the first "fully functional" sheet. I will now copy this sheet to create April 2021 using the following process:
12
Cells formatted green are cells which receive edits when the page is copied to a new month
13
Right click March 2021, Duplicate. Rename "Copy of March 2021" to "April 2021".
14
In cell A2, change it from March to April (if the year rolls over I would update that too)
15
update cell B10 with April's data. in this case lets say 4005.98
16
I repeat this every month. Keep in mind these sheets are oversimplified
17
18
Now, the actual problem I am trying to solve.
19
In March 2021, formulas in A6-A9 and B6-B9 are manually entered. A9 pulls from 1 month ago, A8 pulls from 2 months ago, A7 pulls from 3 months ago, A6 pulls from 4 months ago.
20
In the creation of the April sheet, I am trying to take the string "April 2021" and subtract 1 month to give me "March 2021"..... "January 2021" - 1 month = "December 2020", etc.... so the formulas automatically update
21
In April 2021, I have manually updated the references for A6-9 and B6-9 to illustrate how I want the formulas to automatically update.
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100