Last YTD figures automatically updating each month, to compare with current YTD

[sjm]

Hi, I have last year's report with sales, profit and margin for each month of the financial year.

I will be creating a new report for this financial year and need to compare current YTD with last YTD. YTD for this year is easy. but is there a formula to calculate the comparitive YTD figure for last year?

For example, when May 2007 figures are entered, I want to compare May 2007 with May 2006. When June 2007 figures are entered I want to compare May to June 2007 with May to June 2006 (and so on through the year) but without re-calculating the YTD figures each time for last year.

Any ideas?

Many thanks

[WinteE]

Are the two reports stored in seperate files or in two sheets in the same file ?

[Mark@Work]

Your problem is actually addressing the data from the previous years report.
As our Dutch friend suggested there is an issue that depends on if you have last wears report on a separate sheet of the current workbook or in a completetly separate workbook.
I suspect that you will have the two years in separate workbooks.
However if you have both workbooks open at the same time you very simply enter a cell address from one workbook into the other.
To see how open two new blank workbooks
then type = into cell A1 in Book1 then click on cell B1 of Sheet 1 in Book1 type - and click on cell B1 of Sheet 1 in Book2
you should get:
=B1-[Book2]Sheet1!$B$1
Now however if you save and exit Book 2 your address will become something like:
=B1-'D:\Profiles\My Documents\[Book2.xls]Sheet1'!$B$1
the address will obviously depend on how you've got your computer set up.
This should all work seamlessly, but problems WILL arise if you start moving files to new locations.

[sjm]

Thanks for your replies. It wasn't the looking up into another spreadsheet that was the problem, it was trying to get a formula to work automatically so each month it calculated the previous year to date figure for the same period. I've found out I could do it with an if formula but as the spreadsheet has a long name (which can't be changed as it links to multiple other reports) the formula would be too long. I've cheated and found a slightly more manual way round it.

Thanks anyway!

[Mivy]

I am also in need of the same formula as user "sjm". My data are on the same spreadsheet. Much appreciated if someone could help with this.

Thank you!