Rolling 12-Month Grid

Marvin Daniels

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())))))))))))))

Paul Newcome

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.

Marvin Daniels

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

Paul Newcome

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)

Paul Newcome

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.

Marvin Daniels

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

Paul Newcome

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)

Paul Newcome

@Marvin Daniels Happy to help! 👍️

Marvin Daniels

Thanks for clarifying Paul. I caught that, but forgot to mention it in my original response.