How do you make a dynamically-sized data table?

问题:

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?

回答1:

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)

回答2:

I'm assuming you are using Excel's built in "table"? Not sure how you get that to expand automatically but you can use this formula, that I have tested in 2007, that works.

For the first month use:

=DATE(YEAR(E1),MONTH(E1),1)

Where E1 is the first month you want to start with.

Then use:

=IF(TODAY()>=F2,DATE(YEAR(F2),MONTH(F2)+1,1),"")

Where F2 is just the cell above your current cell. And then just drag it down as far as you want it to go.

回答3:

OK, here's what you would probably need to do.

Tools>Design>Tools>Convert To Range

Use a dynamic name for your new range. You can use the INDEX method or OFFSET method. Then use Conditional Formatting to make it look nice.

I looked in "Excel 2010 Formulas" by John Walkenbach and he doesn't give any solution to the table problem. I think it is because tables are supposed to be automatic, so unfortunately you lose some control when you do that.

You could also automate it with VBA.