Monthly to quarterly timeline

[welshwonder]

Hi,

I have costs over a timeline in months with their assosiated quarter and year numbers like below on a seperate row underneath the month row:

Month 1 Month 2 Month 3 Month 4 etc etc
Y1 Qtr1 Y1 Qtr1 Y1 Qtr1 Y1 Qtr2 etc etc
Monthly costs........................ etc etc

What I need to do is "roll up" the costs (60 months worth) into the correct quarter. I can do the rollup bit, but how do I get a timeline in quarters without any blanks using worksheet functions? i.e.

Y1 Qtr1 Y1 Qtr2 Y1Qtr 3 Y1Qtr4 Y2 Qtr1 Y2 Qtr2 Y2 Qtr3 Y2 Qtr4 etc etc

Thanks in advance....

 

[VBA Geek]

in A1

="Y" & INT((COLUMNS($A1:A1)-1)/4)+1 & " Qtr" & MOD(COLUMNS($A1:A1)-1,4)+1

and drag to the right

 

[welshwonder]

Thanks that does work, but I want to do that but reference the row with the quarterly labels in it as they also have financial year data included which can be dynamically changed by the user. How would I do that?

 

[welshwonder]

Can anyone help?

Just to try and provide more info, I need a worksheet function that will combine the correct monthly data by quarterly header label. The monthly headers exist in the row above the quarterly headers with the costs below:

Month 1 Month 2 Month 3 Month 4 etc etc
Y1 Qtr1 Y1 Qtr1 Y1 Qtr1 Y1 Qtr2 etc etc
Monthly costs........................ etc etc

So I don't need to change the quarterly headers just return the correct one with the correct months sumed under it if that makes sense?

 

[welshwonder]

I guess that since nobody has replied, this must be quite difficult or even impossible?

Can anyone help?

 

[VBA Geek]

Hi

if i undertood well, for each month in row1, you need in row2 the correspondent year and quearter number?

and then sum your data by each month?

 

[welshwonder]

The cost data is monthly so directly relates to the monthly timeline. There is a quarterly header under the monthly header which also shows financial year (worked out earlier on) so I need to sum the data by the relevant correct quarterly header. eg.

Month 1,2 & 3 will be quarter 1 so the three months data is summed under the quarter 1 header etc. Is that clearer? If not, I'll upload an example?

 

[welshwonder]

I've tried to show what I mean in the example below:

Months</SPAN>

M-8 </SPAN>

M-7 </SPAN>

M-6 </SPAN>

M-5 </SPAN>

M-4 </SPAN>

M-3 </SPAN>

M-2 </SPAN>

M-1 </SPAN>

Y1M1 </SPAN>

Y1M2 </SPAN>

Y1M3 </SPAN>

Y1M4 </SPAN>

Y1M5 </SPAN>

Financial Year/Quarter </SPAN>

FY13/14 Qtr 4</SPAN>

FY14/15 Qtr 1</SPAN>

FY14/15 Qtr 1</SPAN>

FY14/15 Qtr 1</SPAN>

FY14/15 Qtr 2</SPAN>

FY14/15 Qtr 2</SPAN>

FY14/15 Qtr 2</SPAN>

FY14/15 Qtr 3</SPAN>

FY14/15 Qtr 3</SPAN>

FY14/15 Qtr 3</SPAN>

FY14/15 Qtr 4</SPAN>

FY14/15 Qtr 4</SPAN>

FY14/15 Qtr 4</SPAN>

Cost Item 1</SPAN>

1</SPAN>

1</SPAN>

1</SPAN>

1</SPAN>

1</SPAN>

1</SPAN>

1</SPAN>

1</SPAN>

1</SPAN>

1</SPAN>

1</SPAN>

1</SPAN>

1</SPAN>

Cost Item 2</SPAN>

10</SPAN>

10</SPAN>

10</SPAN>

10</SPAN>

10</SPAN>

10</SPAN>

10</SPAN>

10</SPAN>

10</SPAN>

10</SPAN>

10</SPAN>

10</SPAN>

10</SPAN>

Cost Item 3</SPAN>

11</SPAN>

11</SPAN>

11</SPAN>

11</SPAN>

11</SPAN>

11</SPAN>

11</SPAN>

11</SPAN>

11</SPAN>

11</SPAN>

11</SPAN>

11</SPAN>

11</SPAN>

Cost Item 4</SPAN>

12</SPAN>

12</SPAN>

12</SPAN>

12</SPAN>

12</SPAN>

12</SPAN>

12</SPAN>

12</SPAN>

12</SPAN>

12</SPAN>

12</SPAN>

12</SPAN>

12</SPAN>

Cost Item 5</SPAN>

13</SPAN>

13</SPAN>

13</SPAN>

13</SPAN>

13</SPAN>

13</SPAN>

13</SPAN>

13</SPAN>

13</SPAN>

13</SPAN>

13</SPAN>

13</SPAN>

13</SPAN>

Monthly Total</SPAN>

47</SPAN>

47</SPAN>

47</SPAN>

47</SPAN>

47</SPAN>

47</SPAN>

47</SPAN>

47</SPAN>

47</SPAN>

47</SPAN>

47</SPAN>

47</SPAN>

47</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=13></COLGROUP>

 

[welshwonder]

Has no one got a solution for this? The issue from my point of view is that you can't use sumif on the Financial Year/Quarter row because it just triples the costs in each of those quarters because the name is the same. So I guess I need a way, on a new row of "reducing" the Financial Year/Quarter labels to the required ones. So instead of having 3 FY14/15 Qtr1 labels, this needs to have just one. Then the logic can be applied to all the others. Then a sumif will work????

 

[welshwonder]

Are there no solutions to this issue? If not, I guess I'll have to try another forum? The concept is straight forward enough (as with most things), I need have a row without any duplicate Fin Yr Qtr named headers so that I can sum the costs (shown in months) for each qtr. Help please.