• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Show Last 12 Months

deciog

Active Member
Sirs, Good Morning.

I need some more help.

I have to show the previous 12 months of the selected, with the values, in Example 1, I tried to assemble a formula, but when it arrives in the month of the previous year I can not find the solution

Thanks in advance, follow the attached example

Decio
 

Attachments

  • Exemplo.xlsx
    12.6 KB · Views: 6
Here's how I would do it.

In C12: Format cell as "mmmm".
Code:
=DATE(YEAR(DATEVALUE($B$13&1)),MONTH(DATEVALUE($B$13&1))-COLUMN(A1),1)
Copy across.

In C13:
Code:
=INDEX($C$3:$N$4,MATCH(YEAR(C12),$B$3:$B$4,0),MATCH(TEXT(C12,"mmmm"),$C$2:$N$2,0))
Copy across.
 

Attachments

  • Exemplo.xlsx
    13.4 KB · Views: 11
Last edited:
Another option for the month formula

C12, copied across :

=EDATE(--("1/"&$B13),-COLUMN(A1))

and, format cell as "mmmm"

p.s. my formula used British Date setting. If your Date system is in USA setting please changed the formula into :

=EDATE(--($B13&"/1"),-COLUMN(A1))


Regards
Bosco
 
Last edited:
Bosco

Thanks, this formula also works perfectly to show the previous months, but I also need to show the values.

Although the colleague made the formula

Decio
 
Last edited:
Bosco

Thanks, this formula also works perfectly to show the previous months, but I also need to show the values.

Although the colleague made the formula

Decio

The "value" in C13, copied across :

=VLOOKUP(YEAR(C12),$B$3:$N$4,MATCH(TEXT(C12,"mmmm"),$B$2:$N$2,0),0)

Regards
Bosco
 
Back
Top