Looping through A3-A13 with monthly numbers (Formulas only)

[deadlyjack]

Hey all Excel warriors,
I'm currently designing a totally new workbook at work which involves every single item within our warehouse, combined with a forecast of the monthly marketing.
I got an idea at work today and started working on a concept that I found logical and might actually work
Now this is still a work in progress and I would like to hear your thoughts.
Since I'm working within forecasting I'd like to remove the previous month-column from my active sheet on the 1st of next month. What I've done this far is write codes that could be a start (somehow) to be used in VBA. This could be totally pointless, or, even if the code-idea may not be usable here, it might work in other places.
So the details:

Row A contains of each month, appearing by:

C1: =PROPER(TEXT(DATE(2050;A2;1);"MMMM"))
D1: =PROPER(TEXT(DATE(2050;A3;1);"MMMM"))
E1: =PROPER(TEXT(DATE(2050;A4;1);"MMMM"))
F1: =PROPER(TEXT(DATE(2050;A5;1);"MMMM"))
G1: =PROPER(TEXT(DATE(2050;A6;1);"MMMM"))
H1: =PROPER(TEXT(DATE(2050;A7;1);"MMMM"))
I1: =PROPER(TEXT(DATE(2050;A8;1);"MMMM"))
J1: =PROPER(TEXT(DATE(2050;A9;1);"MMMM"))
K1: =PROPER(TEXT(DATE(2050;A10;1);"MMMM"))
L1: =PROPER(TEXT(DATE(2050;A11;1);"MMMM"))
M1: =PROPER(TEXT(DATE(2050;A12;1);"MMMM"))
N1: =PROPER(TEXT(DATE(2050;A13;1);"MMMM"))

As you can see, the only thing changing here is the A## within the formula.
Column A Contains the Monthly numerical values, appearing by:

A2: =MONTH(TODAY())
A3: =IF(OR(MONTH(TODAY())+1=13;MONTH(TODAY())+1=14;MONTH(TODAY())+1=15;MONTH(TODAY())+1=16;MONTH(TODAY())+1=17;MONTH(TODAY())+1=18;MONTH(TODAY())+1=19;MONTH(TODAY())+1=20;MONTH(TODAY())+1=21;MONTH(TODAY())+1=22;MONTH(TODAY())+1=23;MONTH(TODAY())+1=24;A2<9);A2-MONTH(TODAY())-2;MONTH(TODAY())+1)
A4: =IF(OR(MONTH(TODAY())+2=13;MONTH(TODAY())+2=14;MONTH(TODAY())+2=15;MONTH(TODAY())+2=16;MONTH(TODAY())+2=17;MONTH(TODAY())+2=18;MONTH(TODAY())+2=19;MONTH(TODAY())+2=20;MONTH(TODAY())+2=21;MONTH(TODAY())+2=22;MONTH(TODAY())+2=23;MONTH(TODAY())+2=24;A2<9);A2-MONTH(TODAY())-1;MONTH(TODAY())+2)
A5: =IF(OR(MONTH(TODAY())+3=13;MONTH(TODAY())+3=14;MONTH(TODAY())+3=15;MONTH(TODAY())+3=16;MONTH(TODAY())+3=17;MONTH(TODAY())+3=18;MONTH(TODAY())+3=19;MONTH(TODAY())+3=20;MONTH(TODAY())+3=21;MONTH(TODAY())+3=22;MONTH(TODAY())+3=23;MONTH(TODAY())+3=24;A2<9);A2-MONTH(TODAY());MONTH(TODAY())+3)
A6: =IF(OR(MONTH(TODAY())+4=13;MONTH(TODAY())+4=14;MONTH(TODAY())+4=15;MONTH(TODAY())+4=16;MONTH(TODAY())+4=17;MONTH(TODAY())+4=18;MONTH(TODAY())+4=19;MONTH(TODAY())+4=20;MONTH(TODAY())+4=21;MONTH(TODAY())+4=22;MONTH(TODAY())+4=23;MONTH(TODAY())+4=24;A2<9);A2-MONTH(TODAY())+1;MONTH(TODAY())+4)
A7: =IF(OR(MONTH(TODAY())+5=13;MONTH(TODAY())+5=14;MONTH(TODAY())+5=15;MONTH(TODAY())+5=16;MONTH(TODAY())+5=17;MONTH(TODAY())+5=18;MONTH(TODAY())+5=19;MONTH(TODAY())+5=20;MONTH(TODAY())+5=21;MONTH(TODAY())+5=22;MONTH(TODAY())+5=23;MONTH(TODAY())+5=24;A2<9);A2-MONTH(TODAY())+2;MONTH(TODAY())+5)
A8: =IF(OR(MONTH(TODAY())+6=13;MONTH(TODAY())+6=14;MONTH(TODAY())+6=15;MONTH(TODAY())+6=16;MONTH(TODAY())+6=17;MONTH(TODAY())+6=18;MONTH(TODAY())+6=19;MONTH(TODAY())+6=20;MONTH(TODAY())+6=21;MONTH(TODAY())+6=22;MONTH(TODAY())+6=23;MONTH(TODAY())+6=24;A2<9);A2-MONTH(TODAY())+3;MONTH(TODAY())+6)
A9: =IF(OR(MONTH(TODAY())+7=13;MONTH(TODAY())+7=14;MONTH(TODAY())+7=15;MONTH(TODAY())+7=16;MONTH(TODAY())+7=17;MONTH(TODAY())+7=18;MONTH(TODAY())+7=19;MONTH(TODAY())+7=20;MONTH(TODAY())+7=21;MONTH(TODAY())+7=22;MONTH(TODAY())+7=23;MONTH(TODAY())+7=24;A2<9);A2-MONTH(TODAY())+4;MONTH(TODAY())+7)
A10: =IF(OR(MONTH(TODAY())+8=13;MONTH(TODAY())+8=14;MONTH(TODAY())+8=15;MONTH(TODAY())+8=16;MONTH(TODAY())+8=17;MONTH(TODAY())+8=18;MONTH(TODAY())+8=19;MONTH(TODAY())+8=20;MONTH(TODAY())+8=21;MONTH(TODAY())+8=22;MONTH(TODAY())+8=23;MONTH(TODAY())+8=24;A2<9);A2-MONTH(TODAY())+5;MONTH(TODAY())+8)
A11: =IF(OR(MONTH(TODAY())+9=13;MONTH(TODAY())+9=14;MONTH(TODAY())+9=15;MONTH(TODAY())+9=16;MONTH(TODAY())+9=17;MONTH(TODAY())+9=18;MONTH(TODAY())+9=19;MONTH(TODAY())+9=20;MONTH(TODAY())+9=21;MONTH(TODAY())+9=22;MONTH(TODAY())+9=23;MONTH(TODAY())+9=24;A2<9);A2-MONTH(TODAY())+6;MONTH(TODAY())+9)
A12: =IF(OR(MONTH(TODAY())+10=13;MONTH(TODAY())+10=14;MONTH(TODAY())+10=15;MONTH(TODAY())+10=16;MONTH(TODAY())+10=17;MONTH(TODAY())+10=18;MONTH(TODAY())+10=19;MONTH(TODAY())+10=20;MONTH(TODAY())+10=21;MONTH(TODAY())+10=22;MONTH(TODAY())+10=23;MONTH(TODAY())+10=24;A2<9);A2-MONTH(TODAY())+7;MONTH(TODAY())+10)
A13: =IF(OR(MONTH(TODAY())+11=13;MONTH(TODAY())+11=14;MONTH(TODAY())+11=15;MONTH(TODAY())+11=16;MONTH(TODAY())+11=17;MONTH(TODAY())+11=18;MONTH(TODAY())+11=19;MONTH(TODAY())+11=20;MONTH(TODAY())+11=21;MONTH(TODAY())+11=22;MONTH(TODAY())+11=23;MONTH(TODAY())+11=24;A2<9);A2-MONTH(TODAY())+8;MONTH(TODAY())+11)

There's a lot going on here, but basically, I noticed that the value of the monthly formula (A2) does not revaluate the condition, if +1 is added when the current number is 12, meaning that after December (month 12) it cannot recognize the pattern to change back to January (month 1). So I used my brain and scribbled some conditions that may restructure the pattern that I would like to have.

The question I have is, will this work?
The core concept is that A2 is based on the current month, period. When the next month comes, all the other values in column A will change accordingly. When this happens, row A will be forced to change automatically.
Now I know, it is only row A that will change, not the entire columns of C to N. But like I said, it's a work in progress. Column A will be hidden later on and I'll focus on the VBA-coding later, like for example; If A2 changes Then cut C2:C10 (in this case) and paste into another sheet.
This way I'll always have the monthly names on the sheet according to the current month always to the left
If I manually change A2 (=MONTH(TODAY())) in the current state to, 1,2,3,4,5,6,7, A3-A13 becomes negative I could use this to an advantage since the month-formula actually reads negatives as months in C1-N1. But I need to figure out the correct algorithm first.

Comment your thoughts, will it work or won't it? If yes, then is there anything else could I do add? Maybe there's a way easier formula for the same thing I've created... Go for it! Share your thoughts!

 

[Skovgaard]

An easier formula in cell A3 could be =MONTH(EDATE(TODAY(),ROW()-2)) and then copy down.

/Skovgaard

 

[Fluff]

Using 365 you could also get rid of col A & just use this in C1 only

=EOMONTH(TODAY(),SEQUENCE(,12,-1))+1

This will give the 1st of every month & you can change the cell format to display the month name

 

[deadlyjack]

Thanks for hittin' me back guys!
You can't blame a guy for trying, right?

@Skovgaard Thanks, this helped me a a lot! Working flawless

@Fluff I tried your suggestion But was left with:

44805

44835​

44866​

44896​

44927​

44958​

44986​

45017​

45047​

45078​

45108​

45139​

wasn't as sure what to add here:
=EOMONTH(TODAY();SEQUENCE(*FILL THIS GAP*;12;-1))+1

 

[Sufiyan97]

Try this with Fluff's formula

Then

 

[Fluff]

@Fluff I tried your suggestion But was left with:

Which is why I said

you can change the cell format to display the month name

wasn't as sure what to add here:
=EOMONTH(TODAY();SEQUENCE(*FILL THIS GAP*;12;-1))+1

Absolutely nothing

 

[deadlyjack]

Thanks, I'll try this out and work on it