Rolling 12-Month Grid
I've built the rolling 12-month grid below, which will be referenced elsewhere in this summary sheet.It works, but this could be cleaner. I know I could remove the "helper" row (row 1), but this is how I worked it out, so I kept it for now. Any suggestions for improvements would be appreciated.
In m1 row 2, I have this formula (columns m2-12 have the same, but the row 1 reference changes):
=IF(MONTH(TODAY()) - [m1]1 = 0, 12, IF(MONTH(TODAY()) - [m1]1 = -1, 11, IF(MONTH(TODAY()) - [m1]1 = -2, 10, IF(MONTH(TODAY()) - [m1]1 = -3, 9, IF(MONTH(TODAY()) - [m1]1 = -4, 8, IF(MONTH(TODAY()) - [m1]1 = -5, 7, IF(MONTH(TODAY()) - [m1]1 = -6, 6, IF(MONTH(TODAY()) - [m1]1 = -7, 5, IF(MONTH(TODAY()) - [m1]1 = -8, 4, IF(MONTH(TODAY()) - [m1]1 = -9, 3, IF(MONTH(TODAY()) - [m1]1 = -10, 2, IF(MONTH(TODAY()) - [m1]1 = -11, 1, MONTH(TODAY()) - [m1]1))))))))))))
In m1 row 3, I have this formula:
=IF(MONTH(TODAY()) - [m1]1 = 0, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -1, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -2, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -3, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -4, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -5, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -6, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -7, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -8, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -9, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -10, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -11, YEAR(TODAY()) - 1, YEAR(TODAY())))))))))))))
Best Answers
-
For the Month row you would use:
=IF(=IF(MONTH(TODAY()) - [m1]$1 < 1, MONTH(TODAY()) + (12 - [m1]$1), MONTH(TODAY()) - [m1]$1)
And for the Year row:
=IF(MONTH(TODAY()) - [m1]$1 < 1, YEAR(TODAY()) - 1, YEAR(TODAY()))
Put those into the [m1] column and you should be able to dragfill to the right.
-
Thanks Paul! That's much cleaner and worked like perfectly and now that I see what you've done, it makes complete sense.
-
For everyone else... It looks like I got a little ahead of myself with the formula for the Month row and threw in an extra "=IF(" at the beginning. That formula will not work as it is above. You will need to adjust it accordingly.
=IF(MONTH(TODAY()) - [m1]$1 < 1, MONTH(TODAY()) + (12 - [m1]$1), MONTH(TODAY()) - [m1]$1)
Answers
-
For the Month row you would use:
=IF(=IF(MONTH(TODAY()) - [m1]$1 < 1, MONTH(TODAY()) + (12 - [m1]$1), MONTH(TODAY()) - [m1]$1)
And for the Year row:
=IF(MONTH(TODAY()) - [m1]$1 < 1, YEAR(TODAY()) - 1, YEAR(TODAY()))
Put those into the [m1] column and you should be able to dragfill to the right.
-
Thanks Paul! That's much cleaner and worked like perfectly and now that I see what you've done, it makes complete sense.
-
For everyone else... It looks like I got a little ahead of myself with the formula for the Month row and threw in an extra "=IF(" at the beginning. That formula will not work as it is above. You will need to adjust it accordingly.
=IF(MONTH(TODAY()) - [m1]$1 < 1, MONTH(TODAY()) + (12 - [m1]$1), MONTH(TODAY()) - [m1]$1)
-
-
Thanks for clarifying Paul. I caught that, but forgot to mention it in my original response.
Help Article Resources
Categories
Check out the Formula Handbook template!
