Rolling 12-Month Grid

Marvin Daniels
Marvin Daniels ✭✭✭✭✭
edited 07/01/20 in Formulas and Functions

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

  • Marvin Daniels
    Marvin Daniels ✭✭✭✭✭
    Answer ✓

    Thanks Paul! That's much cleaner and worked like perfectly and now that I see what you've done, it makes complete sense.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!