How do you make a dynamically-sized data table?

Question

I am using Excel 2010.

I have a "monthly" data table that looks similar to this:

MonthBegin    InventoryExpenses  Overhead  TotalSales  TotalSalesIncome  TotalProfit
July-11       $1,500             $4,952    89          $7,139            $687
August-11     $2,200             $4,236    105         $8,312            $1,876
September-11  $1,100             $4,429    74          $6,691            $1,162

The following formula is automatically propogated to every cell in the [MonthBegin] column:

=DATE( 2011, 7 + ( ROW( ) - 2 ), 1 )

Every other colmun has a similar column-formula that automatically pulls the appropriate data from another source, based on the month listed in the [MonthBegin] column.

With this configuration, I can just insert a new row anywhere into the table and the next month will automatically appear at the bottom in the correct order (which I find nifty).

But I need to take this to the next level of automation, to please management.
How can I make it so that the spreadsheet automatically adds a row for October once the month is over?

I've been considering using a dynamic range for the table:

=OFFSET(A1,0,0,( ( YEAR( TODAY( ) ) - 2011 ) * 12 ) + ( MONTH( TODAY( ) ) - 7 ),6)

... but Excel won't accept such a formula for the table area, I assume because it is not static.
Can anyone explain to me how to gain this functionality with my data table?

Do you want to perpetually add months or roll the months, i.e., add the new month and remove the old month? — Jon49
@Jon49 Perpetually add. The table should get longer as months go by. — Giffyguy

JMax JMax · Accepted Answer · 2011-11-16T09:50:41

You can't dynamically add a new row with formula only.

Here is a VBA event procedure that will do the trick. You need to put in the Workbook module

Option Explicit

Private Sub Workbook_Open()
Dim lo As ListObject
Dim iTot As Long
Set lo = ListObjects("MyTable")
iTot = lo.Range.Rows.Count

'Add this statements before the Range with your worksheet name
'ThisWorkbook.Worksheets("Sheet1")
If Now() > Range("A" & iTot).Value Then
    Range("A" & lo.Range.Rows.Count + 1).Formula = "=DATE( 2011, 7 + ( ROW( ) - 2 ), 1 )"
End If
End Sub

Don't forget to change the name of your table and to add the name of your Worksheet (see the comment inside the code)

How do you make a dynamically-sized data table?

3 Answers