Rolling 12 Profit and Loss Statement

KSU1296

New Member
I currently have a profit & loss statement in excel by our calendar year. I have one workbook for our 2009 financials and one workbook for our 2008 data. Columns and rows in both are the same and the columns are the months, running from January to December, and the rows are the data with each column summed and then a final column showing the total of all twelve months. Like this:

JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC Total
Sales
COGS
ETC...
Total

What I've been asked to do is to setup a rolling p&l as well as our original P&L in a separate worksheet. So, right now in our 2009 financials there would be a separate tab with March 2008 through Feb 2009 showing. Do I have any options instead of just adding the most recently completed month at the end and deleting off the oldest month?

I can combine the two workbooks into one to get all the data together if I need to, but I'm looking for some way to make it dynamic such that Excel knows based on the date to only visibly show the last twelve months from the date. So, if I open the workbook today (March 5th) it will show the last twelve months, but if I open it April 2nd, it will show April 08-March 09.

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

MickG

MrExcel MVP
Hi, If you combine the Years into one sheet and your Headers are dates i.e :-Jan-08 Feb-08 Mar-08 Apr-08,---Jan-09--, then the following code will hide the Columns a Year old or older.
Code:
``````Sub Hide()
Dim Rng As Range, Ac As Range
Set Rng = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
For Each Ac In Rng
If Month(Ac) < Month(Now) And Year(Ac) < Year(Now) Then
Ac.Columns.Hidden = True
End If
Next Ac
End Sub``````
Regards Mick

KSU1296

New Member
I'm not a VBA guy though. How do I put actually put the code into my file?

Replies
0
Views
293
Replies
1
Views
142
Replies
3
Views
896
Replies
1
Views
115
Replies
2
Views
144

1,196,020
Messages
6,012,902
Members
441,739
Latest member
Jeezer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back