Formula to state the previous 12 months in text, based on changing contend in a cell

Anandpersad

New Member
Joined
Oct 18, 2014
Messages
31
Hi All,
I need help with the following issue:
I have a database (Sheet name is Data) in which data is updated monthly.

Based on this data base I have put the following formula in an additional sheet (sheet 2). In cell O8, I have the following formula: =LOOKUP(2;1/(Data!K:K<>"");Data!K:K)
This will go in sheet "data", go to the last row which is not empty, and give the month name as mentioned there in column K).
What I am looking now, is a formula which will give the names of the previous 12 months in "mmm"
So cell N8, wil give December, cell M8 will give November,.......... and cell C8 will give January.

If the name of the month in cell O8 changes to e.g. Februari, the month names in the cell (c8...N8) has to change accordingly.

Thanks for your help

Anand
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi Anand

in cell n8 put =DATE(YEAR(B2),MONTH(B2),1) replacing b2 with the lookup formula
then in m8 put =DATE(YEAR(N8),MONTH(N8)-1,1) and copy it back to c8
then format the cells c8 to n8 as custom format MMM
 
Upvote 0
Is the value in O8 an actual date or is it text like Jan
 
Upvote 0
Is the value in O8 an actual date or is it text like Jan
Hi ,

The formula is O8 is =LOOKUP(2;1/(Data!K:K<>"");Data!K:K)
"Jan", is the outcome from the formula. The data in column "K" in the file Data is text, hard text, not a formula
 
Upvote 0
Hi Anand

in cell n8 put =DATE(YEAR(B2),MONTH(B2),1) replacing b2 with the lookup formula
then in m8 put =DATE(YEAR(N8),MONTH(N8)-1,1) and copy it back to c8
then format the cells c8 to n8 as custom format MMM

Hi Rondeondo

Thank you for your help. Much appreciated.

If I copy the formula (=DATE(YEAR(LOOKUP(2;1/(Data!K:K<>"");Data!K:K));MONTH(LOOKUP(2;1/(Data!K:K<>"");Data!K:K));1)) as you mention to cell N8, the outcome is *Value!
Here B2 is replaced by LOOKUP(2;1/(Data!K:K<>"");Data!K:K)
 
Upvote 0
How about in N8 copied right
=TEXT(EDATE(O8&1,-1),"mmm")
 
Upvote 0
ok if your lookup is returning Jan just wrap it with date(year(now),<formula for lookup>,1) to make it be a date. My formula relies on o8 being a date. You can then set the cell display to show just the month.
 
Upvote 0
I believe you can do this without calling out to the EDATE function...

=TEXT((O8&1)-1,"mmm")

Hi Rick,
Can it be that formula as you mentioned is only working from N8 to E8. I copied the formula from cell N8 till C8. But cell D8 and C8 gives #VALUE!.

Second question: To get the month in English (Jan, Feb, Mrt,......Oct,Nov and Dec) is used the following code "[$-409]" . However, adding this part to your formula, gives me an error (#VALUE!) from cell K8. So cell N8, M8 and L8 gives the month as requested (Dec, Nov and Oct). Cell K8 give #VALUE!. Can you help me here?
 

Attachments

  • Example1.PNG
    Example1.PNG
    9.7 KB · Views: 11
Upvote 0
The formula I posted is actually Fluff's formula with a small tweak. I haven't been following this thread... I simply commented on what I saw that he posted. You might want to attach a picture that is readable though as I cannot make out anything on what you posted. Better would be to upload an actual copy of the workbook to, say, DropBox so we can see your actual data and how the formula interacts with it.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top