Save column formulas in the table header
Is it possible for the formulas I need to apply to the columns to be saved or applied to the column header or some metadata so that when and when I add new rows to the Excel spreadsheet the Formulas are applied to the columns?
Scenarion
I am creating a template table with no rows at first. On a separate sheet (or the same sheet for that matter), when 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 that I cannot have any rows in the table at the start OR the user can delete rows manually. When I add new rows programmatically, I want the formulas to apply to the cells as well. Most of the formulas I use are of three types:
Structured table reference, Excel functions like SUM, AVERAGE, etc. and custom function names.
Updated:
Here's what I've tried:
1> tried to apply the formula to the title itself. Result: the title itself changes with an error #REF!
. I believe the behavior is correct. So this is a no option.
2> Tried creating one row and applying the formula to the row. This works, but the problem is I don't want to start with a dummy string.
3> Using VBA code to add a row to a table with
ActiveWorkbook.Worksheets("Sheet3").ListObjects("Table2").ListRows.Add AlwaysInsert:=True
inside a for loop. New lines preserve visual style sheets, but don't seem to preserve formulas. Just blank cells.
source to share
Can fomrmulas be header over cellular networks?
And then using 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
source to share
Not sure about table templates or VBA, but maybe there is another option using =ARRAYFORMULA()
For example, let's say you have a header row and 3 columns and you want your last column to be the product of the first two. In a cell, C2
you can enter the following:
=ARRAYFORMULA(A2:A*B2:B)
This has three advantages :
- Skips the first line completely
- Effectively applies a formula to each row, which is useful if you later decide to insert a row (your question)
- Only one place to change the formula for each separate line
Although, it may not be immediately obvious where, like /, where cells are counted. (hint: ctrl
+ ~
might help)
source to share