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?
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With