Auto Populate quarters by just typing in the number of years

[sugar_lips]

Hi Folks,

I'm struggling with this one! I need a formula that can automatically populate yearly quarters in separate columns based on a number of years.
So if I put in 5 years - excel would populate 20 columns with Quarter 1 - Quarter 2 and so on. If I then change it to 3 years it would only populate 12 columns.

Is there a way to do this somehow?

Thank you so much!

[nathansav]

I think you'll need the formulas there, dependant on an IF, whether they show.

So you'd have

=if(a1=1,"Q1 Yr 1","")
=if(a1=1,"Q2 Yr 1","")
=if(a1=1,"Q3 Yr 1","")
=if(a1=1,"Q4 Yr 1","")
=if(a1=2,"Q1 Yr 2","")
=if(a1=2,"Q2 Yr 2","")
=if(a1=2,"Q3 Yr 2","")
=if(a1=2,"Q4 Yr 2","")

etc.

[sktneer]

Assuming you input no. of years in A2, then
In B2

Please Login or Register  to view this content.

and copy down.

Is this what you are trying to achieve?

[sugar_lips]

Assuming you input no. of years in A2, then
In B2

Please Login or Register  to view this content.

and copy down.

Is this what you are trying to achieve?

Yes thank you! I would now just like to be able to put them 1 quarter in each column instead of in one columns with a quarter in each row. Is this possible somehow?

And each quarter shouldn't come out as "Quarter 1" but Jul-14 then Oct-14 then Jan 15... etc. Does this make sense?

Thank you again!

[Tony Valko]

Try it like this:

Entered in B2 and copied across until you get blanks.

=IF(COLUMNS($B2:B2)<=$A2*4,"Quarter "&COLUMNS($B2:B2),"")

[sugar_lips]

Try it like this:

Entered in B2 and copied across until you get blanks.

=IF(COLUMNS($B2:B2)<=$A2*4,"Quarter "&COLUMNS($B2:B2),"")

this worked thank you!!! Now for the last step.

Is it possible for the "Quarter1" to come out as a date like Jul-14 then Quarter 2 to come out as Oct-14? Thank you so much for all the help already!

[daddylonglegs]

Try putting the first date in B2 and then this version of Tony's formula in C2 copied across

=IF(COLUMNS($B2:C2)<=$A2*4,EDATE(B2,COLUMNS($C2:C2)*3),"")

custom format all dates as mmm-yy