Fill in missing cells

[Wayne Burritt]

Hi: I have the following data arrangement...

B C D E F G H I
J K L

2004
Q1

Column B year and quarter are input cells, which can be any quarter (Q1-Q4)
and any year. What I want to do program a macro that automatically fills in
historical data for the year and quarter up to column L. The data should be
declining, by quarter like this...

B C D E F G H I
J K L

2004 2003 2003 2003 etc
Q1 Q4 Q3 Q2 etc

Thanks. Wayne

 

[Frank Kabel]

Hi
the simples way (without VBA) would be:
1. in B2 enter a real date with your starting quarter. e.g.
10/01/2004

2. Format cell B2 with the custom format "YYYY"

3. In C2 enter the formula
=DATE(YEAR(B2),MONTH(B2)-3,1)
and copy this to the right (until column L)

4. In B3 enter the formula
="Q" & INT((MONTH(B2)-1)/3)+1
and copy to the right