Keeping column formulas in a table header

Question

Is it possible to have the formulas that I need applied on columns be saved or applied to a column header or some kind of metadata so that as and when I add new rows to my Excel table the Formulas get applied to the columns?

Scenarion:

I am creating a template Table, which will have no rows at first. On a separate sheet (or same sheet for that matter) once the user selects the number of rows to be generated in the table, I dynamically add rows to the table using VBA.

The idea is I may not have any rows in the table at beginning OR user may have deleted rows manually. When I programmatically add new rows, I want the Formulas applied on the cells as well. Most of the formulas I am using are either of the three types:

Structured table reference, Excel functions like SUM, AVERAGE etc and custom function names.

Updated:

Here is what I have tried:

1> tried applying the formula to the header itself. Result: The header it self changes with #REF! error. I think the behavior is correct. So it's a no-go option.

2> Tried creating one row and apply the formula to the row. That works, but the problem is, I do not want a dummy row to begin with.

3> Using VBA code to add row to the table using

ActiveWorkbook.Worksheets("Sheet3").ListObjects("Table2").ListRows.Add AlwaysInsert:=True

inside a for loop. The new rows retain the visual style sheets, but does not seem to retain the formulas. Just blank cells.

If you want to have this automated: I think you may want to store the count of rows on the sheet globally then check if it has changed using Worksheet_Change event and if it's > than current then you may want to enter some formulas. If you don't just drag down your formulas manually — user2140173
@MarkFitzgerald Updated the post with the things I have tried so far. — Ayusman
If you are using vba to create the rows, then why not just have the code also populate the formula in to the cells? — Petay87
@Petay87 yes this is a good suggestion. I did realize I could do that for each cell of each row I am inserting, however the number of columns is approximately 70 and most formulas are really long. I wanted to approach this problem by providing a pre-configured template. — Ayusman

Daniel Dušek Daniel Dušek · Accepted Answer · 2014-09-09T14:39:07

Could the fomrmulas be in header cell commnets?

enter image description here

And then with VBA add the formula for the current row:

Sub test()
    Dim headerCells As Range
    Set headerCells = Range("B2:E2")

    OnNewRow 3, headerCells
End Sub

Sub OnNewRow(newRow As Integer, headerCells As Range)
    Dim headerCell As Range, targetCell As Range, formulaFromComment As String
    For Each headerCell In headerCells
        formulaFromComment = GetFormulaFromComment(headerCell)
        If (formulaFromComment = "") Then _
            GoTo NextHeaderCell
        Set targetCell = Intersect(headerCells.Worksheet.Rows(newRow), _
                                   headerCell.EntireColumn)
        AddFormula newRow, targetCell, formulaFromComment
NextHeaderCell:
    Next
End Sub

Sub AddFormula( _
    newRow As Integer, _
    targetCell As Range, _
    formula As String)

    formula = Replace(formula, "{ROW}", newRow)
    targetCell.formula = formula
End Sub

Function GetFormulaFromComment(headerCells As Range) As String
    ' TODO
    GetFormulaFromComment = "=SUM($C${ROW}:$E${ROW})"
End Function

Keeping column formulas in a table header

Updated:

2 Answers