Automatic Population of Rows for 12 Quarters from Selected Quarter

[gannybun]

Apologies in advanced as my excel is unable to load XL2bb. Can someone help me with this.

I've this in my drop down list
Part of the Drop down List for selection
1Q24
2Q24
3Q24
4Q24
1Q25
2Q25
3Q25
4Q25
1Q26
2Q26
3Q26
4Q26
1Q27
2Q27
3Q27
4Q27
(to be expanded)

Example 1 : when i select "2Q24". i want to create a Row in excel that will be automatically populated when qtr(2Q24) in list is selected, spanning 12 quarters starting from the selected quarter.
("|" being the cell, no need to formulate "|" )

| 2Q24 | 3Q24 | 4Q24 | 1Q25 | 2Q25 | 3Q25 | 4Q25 | 1Q26 | 2Q26 | 3Q26 | 4Q26 | 1Q27 |

Example 2 : when i select "4Q25". i want to create a Row in excel that will be automatically populated when qtr(4Q25) in list is selected, spanning 12 quarters starting from the selected quarter.

| 4Q25 | 1Q26 | 2Q26 | 3Q26 | 4Q26 | 1Q27 | 2Q27 | 3Q27 | 4Q27 | 1Q28 | 2Q28 | 3Q28 |



many thanks in advanced

 

[Eric W]

Try:

Book2
	C	D	E	F	G	H	I	J	K	L	M	N
1	Selection
2	2Q24
3
4	2Q24	3Q24	4Q24	1Q25	2Q25	3Q25	4Q25	1Q26	2Q26	3Q26	4Q26	1Q27
Sheet3

Cell Formulas
Range	Formula
C4:N4	C4	=LET(s,SEQUENCE(,12,RIGHT(C2,2)+(LEFT(C2)-1)*0.25,0.25),MOD(s,1)*4+1&"Q"&INT(s))
Dynamic array formulas.

 

[gannybun]

let me give it a shoot!

 

[gannybun]

Try:

Book2
	C	D	E	F	G	H	I	J	K	L	M	N
1	Selection
2	2Q24
3
4	2Q24	3Q24	4Q24	1Q25	2Q25	3Q25	4Q25	1Q26	2Q26	3Q26	4Q26	1Q27
Sheet3

Cell Formulas
Range	Formula
C4:N4	C4	=LET(s,SEQUENCE(,12,RIGHT(C2,2)+(LEFT(C2)-1)*0.25,0.25),MOD(s,1)*4+1&"Q"&INT(s))
Dynamic array formulas.

it works!!! thank you so much!!!

If i would to put the 12 qtrs in a column, how should i modified the formula?

 

[Eric W]

You just need to move the first comma in the SEQUENCE like this:

=LET(s,SEQUENCE(12,,RIGHT(C2,2)+(LEFT(C2)-1)*0.25,0.25),MOD(s,1)*4+1&"Q"&INT(s))