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

[Anandpersad]

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

 

[rondeondo]

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

 

[Fluff]

Is the value in O8 an actual date or is it text like Jan

 

[Anandpersad]

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

 

[Anandpersad]

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)

 

[Fluff]

How about in N8 copied right
=TEXT(EDATE(O8&1,-1),"mmm")

 

[rondeondo]

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.

 

[Rick Rothstein]

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

I believe you can do this without calling out to the EDATE function...

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

 

[Anandpersad]

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?

 

[Rick Rothstein]

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.