You are asking for a fairly extensive amount of Visual Basic code, or a lot of formulas.
I can offer you some suggestions to help you get to your solution, but not a complete solution.
I am suggesting formulas as these are likely to be more useful as building blocks for your solution.
1. Organize your data in rows with monthly headings in the top-most row. (Headings in columns E to AC for this example Jan-08 to Jan-10)
The headings are the 1st day of each month. Enter valid Excel dates - then format as "mmm-yy"
By using dates that Excel recognizes you will be able to use functions such as SUMPRODUCT() to find ranges of data, as well as being able to use HLOOKUP() to find data in specific columns.
2. Use the headings in row 1 to create a drop-down list, for users to select a month.
In Cell A1 is the heading "Select Month"
Cell A2 is the cell used to select the month
I added a drop down list of months/years using the headings in cells E12 to AB1
Select cell A2 then from the Ribbon - Data - Data Validation - Data Validation - Allow: List and select cells E1 to AC1 as the Source:
Format cell A2 with "mmm-yy"
3. As you want headings to reflect the selected month and current month, create two date texts that can be included in other headings.
In cell A4 Enter this formula:=TEXT(A2,"mmm-yy")
This will show the selected month/year
In cell A5 Enter this formula:=TEXT(NOW(),"mmm-yy")
This will show this month/year
If you want a heading in cell B6 for % Change use this formula= "Percentage Change" & CHAR(10) & $A$4 & " to " & $A$5
The CHAR(10) forces the date range on to a second line. Increase the row height and format the cell with text wrapping.
4. Here are three formulas that will return useful building blocks for your formulas.
4a The difference between the data this month and the selected month =HLOOKUP(DATE(YEAR(NOW()),MONTH(NOW()),1),E1:AC3,2,FALSE)-HLOOKUP(DATE(YEAR(A2),MONTH(A2),1),E1:AC3,2,FALSE)
Note that this uses the first day of the month as the index to lookup in the HLOOKUP() function. For this to work the dates in row 1 must have been entered as the 1st of each month, even if formatted without the day showing.
For data on subsequent rows increase the range E1:AC3 e.g. E1:AC10
and increase the row to get the data from e.g., HLOOKUP(DATE(YEAR(NOW()),MONTH(NOW()),1),E1:AC10,10,FALSE)
4b The avarage of values between two dates (inclusive of the earlier month and exclusive of the current month)
In the above formula
the first part selects only months equal to or later than the selected month
the second part selects only months less than last month [note the -1 after Month(now())]
and for months that meet both criteria it adds the values in row 2 - it does this because each cell that doesn't meet the date criteria returns zero which when multiplied by the value in row 2 returns zero. Cells that match the date criteria return 1 and this is multiplied by the value in row 2, returning the row 2 values -
this returns the total or sum for the months and this is divided by the number of months which are selected by the same process - all cells meeting the date criteria return 1, so when 12 months meet the criteria this returns 12.
This part of the formula can be used on its own to show the number of months selected.
4c A heading using the month count formula:
="Average"&CHAR(10)&A4&" to "&$A$5&CHAR(10)&TEXT(SUMPRODUCT(($E$1:$AC$1>=A2)*($E$1:$AC$1<=DATE(YEAR(NOW()),MONTH(NOW())-1,1))),"#0") & " months"
If Feb-09 was selected this shows:
Feb-09 to Jan-10
Hope this helps.
I don't mind trying to answer specific issues that arise as you work through this.