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
|