Time-shifting information

G

Guest

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
 
J

JE McGimpsey

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Really need some help with a tricky formula 8
?? VLookup ?? 2
Lookup expiration dates by calendar month 4
Finding values based on current month 3
Using the results of formulae between different spreadsheets. 1
INT((MONTH($C$1:$C$7)+2)/3)=$A$2 2
Payroll 1
Time format to number while keeping value 2

Top