If cell contains text - return this value and the next 12 rows

[CathalP1992]

Hi there,

Be really grateful for any help on this.

I have a called Front Month where I type manually the current month and year (ie. APR23)

I am looking for a simple way to tell excel if I change this value, that excel should find the next matching value and the X number of rows below it.

So if I change front month value, the series of month values will change based on the front month. There is another helper sheet where I have a list of months from Jan23-Dec25 so that my list will chose the next 12 consecutive.

Any help greatly appreciated.

Cathal.

 

[Sufiyan97]

Try

Book6
	A	B	C
1	Jan23	Jan23
2		Feb23
3		Mar23
4		Apr23
5		May23
6		Jun23
7		Jul23
8		Aug23
9		Sep23
10		Oct23
11		Nov23
12		Dec23
13
Sheet1

Cell Formulas
Range	Formula
B1	B1	=A1
B2:B12	B2	=EOMONTH(B1,1)

Custom format - mmmyy

 

[Crystalyzer]

Book1
	A	B	C	D	E
7		Mar23	Mar23		Jan23
8			Apr23		Feb23
9			May23		Mar23
10			Jun23		Apr23
11			Jul23		May23
12			Aug23		Jun23
13			Sep23		Jul23
14			Oct23		Aug23
15			Nov23		Sep23
16			Dec23		Oct23
17			Jan24		Nov23
18			Feb24		Dec23
19					Jan24
20					Feb24
21					Mar24
22					Apr24
Sheet1

Cell Formulas
Range	Formula
C7	C7	=INDEX(Months,MATCH($B$7,Months,0))
C8	C8	=INDEX(Months,MATCH($B$7,Months,0)+1)
C9	C9	=INDEX(Months,MATCH($B$7,Months,0)+2)
C10	C10	=INDEX(Months,MATCH($B$7,Months,0)+3)
C11	C11	=INDEX(Months,MATCH($B$7,Months,0)+4)
C12	C12	=INDEX(Months,MATCH($B$7,Months,0)+5)
C13	C13	=INDEX(Months,MATCH($B$7,Months,0)+6)
C14	C14	=INDEX(Months,MATCH($B$7,Months,0)+7)
C15	C15	=INDEX(Months,MATCH($B$7,Months,0)+8)
C16	C16	=INDEX(Months,MATCH($B$7,Months,0)+9)
C17	C17	=INDEX(Months,MATCH($B$7,Months,0)+10)
C18	C18	=INDEX(Months,MATCH($B$7,Months,0)+11)

Named Ranges
Name	Refers To	Cells
Months	=Sheet1!$E$7:$E$21	C7:C18

 

[CathalP1992]

Try

Book6
	A	B	C
1	Jan23	Jan23
2		Feb23
3		Mar23
4		Apr23
5		May23
6		Jun23
7		Jul23
8		Aug23
9		Sep23
10		Oct23
11		Nov23
12		Dec23
13
Sheet1

Cell Formulas
Range	Formula
B1	B1	=A1
B2:B12	B2	=EOMONTH(B1,1)

Custom format - mmmyy

Thanks that worked. Last question, is there a specific way to ensure the letters are in capitals? Its for a specific function and the letter of the month need to be uppercase?

 

[CathalP1992]

Book1
	A	B	C	D	E
7		Mar23	Mar23		Jan23
8			Apr23		Feb23
9			May23		Mar23
10			Jun23		Apr23
11			Jul23		May23
12			Aug23		Jun23
13			Sep23		Jul23
14			Oct23		Aug23
15			Nov23		Sep23
16			Dec23		Oct23
17			Jan24		Nov23
18			Feb24		Dec23
19					Jan24
20					Feb24
21					Mar24
22					Apr24
Sheet1

Cell Formulas
Range	Formula
C7	C7	=INDEX(Months,MATCH($B$7,Months,0))
C8	C8	=INDEX(Months,MATCH($B$7,Months,0)+1)
C9	C9	=INDEX(Months,MATCH($B$7,Months,0)+2)
C10	C10	=INDEX(Months,MATCH($B$7,Months,0)+3)
C11	C11	=INDEX(Months,MATCH($B$7,Months,0)+4)
C12	C12	=INDEX(Months,MATCH($B$7,Months,0)+5)
C13	C13	=INDEX(Months,MATCH($B$7,Months,0)+6)
C14	C14	=INDEX(Months,MATCH($B$7,Months,0)+7)
C15	C15	=INDEX(Months,MATCH($B$7,Months,0)+8)
C16	C16	=INDEX(Months,MATCH($B$7,Months,0)+9)
C17	C17	=INDEX(Months,MATCH($B$7,Months,0)+10)
C18	C18	=INDEX(Months,MATCH($B$7,Months,0)+11)

Named Ranges
Name	Refers To	Cells
Months	=Sheet1!$E$7:$E$21	C7:C18

this worked thank you. Last question is it possible to get the letters of the month in upper case?

 

[Crystalyzer]

Book1
	A	B	C	D
1	Feb23	FEB23		Jan23
2		MAR23		Feb23
3		APR23		Mar23
4		MAY23		Apr23
5		JUN23		May23
6		JUL23		Jun23
7		AUG23		Jul23
8		SEP23		Aug23
9		OCT23		Sep23
10		NOV23		Oct23
11		DEC23		Nov23
12		JAN24		Dec23
13				Jan24
14				Feb24
15				Mar24
16				Apr24
Sheet1

Cell Formulas
Range	Formula
B1:B12	B1	=UPPER(INDEX(Months,MATCH($A$1,Months,0)+ROW()-1))

Named Ranges
Name	Refers To	Cells
Months	=Sheet1!$D$1:$D$15	B1:B12

 

[Sufiyan97]

Thanks that worked. Last question, is there a specific way to ensure the letters are in capitals? Its for a specific function and the letter of the month need to be uppercase?

Try

Book8
	A	B	C
1	Jan23	JAN23
2		FEB23
3		MAR23
4		APR23
5		MAY23
6		JUN23
7		JUL23
8		AUG23
9		SEP23
10		OCT23
11		NOV23
12		DEC23
13
Sheet1

Cell Formulas
Range	Formula
B1:B12	B1	=UPPER(TEXT(EOMONTH($A$1,ROW()-1),"mmmyy"))

 

[CathalP1992]

Try

Book8
	A	B	C
1	Jan23	JAN23
2		FEB23
3		MAR23
4		APR23
5		MAY23
6		JUN23
7		JUL23
8		AUG23
9		SEP23
10		OCT23
11		NOV23
12		DEC23
13
Sheet1

Cell Formulas
Range	Formula
B1:B12	B1	=UPPER(TEXT(EOMONTH($A$1,ROW()-1),"mmmyy"))

Im probably being real picky right now and i have tried to play around with the code myself but is it possible to have the top ones equal to eachother and then the months below update according to the first

 

[Sufiyan97]

Try

Book5
	A	B	C
1	Front Month	Month
2	Mar23	MAR23
3		APR23
4		MAY23
5		JUN23
6		JUL23
7		AUG23
8		SEP23
9		OCT23
10		NOV23
11		DEC23
12		JAN24
13		FEB24
14
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=UPPER(TEXT(EOMONTH($A$2,ROW()-2),"mmmyy"))

 

[CathalP1992]

Try

Book5
	A	B	C
1	Front Month	Month
2	Mar23	MAR23
3		APR23
4		MAY23
5		JUN23
6		JUL23
7		AUG23
8		SEP23
9		OCT23
10		NOV23
11		DEC23
12		JAN24
13		FEB24
14
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=UPPER(TEXT(EOMONTH($A$2,ROW()-2),"mmmyy"))

if you had to explain that what will the diff numbers represent? 1-12 the number of the month from the initial month