Period to date

[Guest]

trying to create a running reference to prior year to date. I have a 2006
spreadsheet with monthly amounts. In my 2007 spreadsheet, I want to
reference the prior year to date numbers. In the month of July, I want to
see a Jan -July 2006 total; in the month of August, I want to see Jan - Aug
2006 total etc, etc. I'd like to automate this process so that there's not a
lot of manual entry each month.

 

[Sandy Mann]

With the 2006 sheet named 2006 Budget, this will sum the values to the month
end when the first date of the next month is entered in the 2007 Budget
sheet:

=IF(AND(A3<>"",MONTH(A3)<>MONTH(A2)),SUMPRODUCT((MONTH('2006
Budget'!$A$2:$A$1005)<=MONTH(A2))*('2006 Budget'!$E$2:$E$1005)),"")

Assuming Dates in Column A, Amounts in Column E in both sheets.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk