Time-shifting information
 

hi,

I have a 24 month business plan, column headings are 1 through to 24. The
first month of the plan can be any month of any year. How do I, on a
separate sheet, show the calendar year (or part calendar year) results
without having to change the headings from 1 to 24 in the first sheet???

Hope this makes sense!

Kind regards

Amanda
i.e.
1 2 3 4 5 etc
A. income £10 £20 £30 £40 £50
B. expenditure £11 £22 £33 £44 £55

Example where 1 = Sept 07, 2 = Oct 07 etc

Sheet 2, calendar year
Yr1 Yr2
A. income £100 £50
B. expenditure £110 £55

Time-shifting information
 

How should XL know which month is the starting month?

If you have the starting date in another cell, say, sheet2!J1, and
income start in sheet1!B2,then

YR1 income: =SUM(OFFSET(Sheet1!$B$2,,,1,13-MONTH(J1)))
YR2 income: =SUM(OFFSET(Sheet1!$B$2,,13-MONTH(J1),1,12))

similarly for expense.


In article ,
Amanda wrote:

hi,

I have a 24 month business plan, column headings are 1 through to 24. The
first month of the plan can be any month of any year. How do I, on a
separate sheet, show the calendar year (or part calendar year) results
without having to change the headings from 1 to 24 in the first sheet???

Hope this makes sense!

Kind regards

Amanda
i.e.
1 2 3 4 5 etc
A. income £10 £20 £30 £40 £50
B. expenditure £11 £22 £33 £44 £55

Example where 1 = Sept 07, 2 = Oct 07 etc

Sheet 2, calendar year
Yr1 Yr2
A. income £100 £50
B. expenditure £110 £55