Excel – How to set Function “formula” for whole column in Excel 2007

To just clarify Mureinik's answer a little bit, because it's a good start, for column MyColumn you could use: OFFSET($A$1,0,MyColumn-1,ROWS($A:$A),1)

Start at $A$1, and then offset by 0 rows and (your column number minus 1), using a height of ROWS($A:$A) and a width of 1. Max rows in recent versions is 1,048,576. You can use a smaller height if you know what the maximum height is likely to be.

So to sum column 3:

=SUM(OFFSET($A$1,0,3-1,ROWS($A:$A),1))

The answer from sgp667 is not bad, but note that SUBSTITUTE could be a little messy and slow if you are using it a lot.

To reference another sheet, just change the reference to $A$1, so for instance: SUM(OFFSET(Sheet2!$A$1,0,3-1,ROWS($A:$A),1))

Here is a small demo that you can adapt to your actual schema. In the demo:

1. the column of interest is column B
2. if more than one row is inserted, nothing will happen
3. if a row below the data area is inserted, nothing happens
4. if a row is deleted, nothing happens
5. if a row in the middle of the data is inserted, the cell in column B will be filled with either the formula above or the formula below
6. if there is no formula above or below the new cell, nothing happens

Insert the following sub in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, r1 As Range, r2 As Range

    Set r = Target
    If r.Rows.Count > 1 Then Exit Sub
    If r.Columns.Count <> Cells.Columns.Count Then Exit Sub
    If r.Row = 1 Then Exit Sub

    Set r1 = Intersect(r, Columns(2))
    If r1.Value <> "" Then Exit Sub

    If r1.Offset(-1, 0).HasFormula Then
        Application.EnableEvents = False
            r1.Offset(-1, 0).Copy r1
        Application.EnableEvents = True
        Exit Sub
    End If

    If r1.Offset(1, 0).HasFormula Then
        Application.EnableEvents = False
            r1.Offset(1, 0).Copy r1
        Application.EnableEvents = True
        Exit Sub
    End If

End Sub