Reputation: 61074

Excel: Insert formulas in a dynamic range

The context:

Depending on the size of a few input columns, I'd like to have a column with formulas that adjust to the size of those inputcolumns. I know how to do this with VBA, but I would really like to know if there's another way to do this.

The details:

This is a basic setup where columns A and B are values, and column C consists of a simple formula where specifically range C2 has the formula =A2+B2. Now i can make the columns A and B dynamic ranges by using Formulas > Name Manager and inserting the formulas rng_A=OFFSET(Sheet1!$A$2;;;COUNTA(Sheet1!$A$2:$A$40);) and rng_B=OFFSET(Sheet1!$B$2;;;COUNTA(Sheet1!$B$2:$B$40);) like this:

This comes in handy for charts where you can use the named ranges for a dynamic display of the data like this:

Now, If I insert two new numbers in column A, the chart would automatically add them to the line:

The question:

If I'd like to add column C in the chart, that could be done just as easily by adding a new named range, but I would have to insert two new formulas in range C8 and C9. Is it in any way possible to make column C a dynamic range that consists of formulas? Without filling the entire column with formulas such as =IF(A8="";"";A8+B8) here:

Or using a VBA snippet?

Than you for any suggestions!

Upvotes: 1

Answers (4)

Weylin Piegorsch

Reputation: 64

Tables are a great way to accomplish this, I won't knock it. If instead you specifically want a formulaic approach without either VBA or named ranges, then you have FILTER().

A simplistic approach:

FILTER(A:A,A:A<>"")

However, your input data has a header, so you have to filter that out too:

FILTER(A:A,(A:A<>"")*(A:A<>A1))

The asterisk here acts as a Boolean AND. Each of the sub-tests returns an array of TRUE/FALSE a million entries long (the max size of a column) but you can let Excel deal with that nuance. The multiplication converts TRUE/FALSE into 1/0. 1*1=1, everything else =0, so it essentially results in an array of a million 1/0 result that define the criteria of which cells in your input column (also a million entries) pass through the filter.

So C2 then becomes:

=FILTER(A:A,(A:A<>"")*(A:A<>A1))+FILTER(B:B,(B:B<>"")*(B:B<>B1))

If you want to handle the potential that you have only A or B populated but not both, you can wrap that in an IFERROR function. Or allow the #N/A error to exist; up to you. I'm sure there's a way to curtail the spilled array at C2# so that it's only as long as the shorter of A or B, but I'll leave that as an exercise for the reader.

Upvotes: 0

Aaron Pan Vega

Reputation: 174

In the sheet code, using the change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Or Target.Column = 2 Then
Range("C" & Target.Row).Formula = "=A" & Target.Row & "+B" & Target.Row
End If
End Sub

Upvotes: 1

Variatus

Reputation: 14373

Consider just using an event procedure like this one.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range

    If Target.Cells.CountLarge = 1 Then
        Set Rng = Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp).Offset(0, 1))
        If Not Application.Intersect(Target, Rng) Is Nothing Then
            Application.EnableEvents = False
            With Target
                Set Rng = Range(Cells(.Row, "A"), Cells(.Row, "C"))
            End With
            With Rng
                .Cells(3).Value = .Cells(1).Value + .Cells(2).Value
            End With
            Application.EnableEvents = True
        End If
    End If
End Sub

Whenever you make a change to either A or B the total is made in column C. The formula could be tweaked to write zero, or blank out the cell, if only column B has a value.

Upvotes: 1

jsheeran

Reputation: 3037

Use a table (select your range and then Insert > Table) and make C a calculated column.

This way, when you add more rows to the table, the formula applies to column C in that row.

Upvotes: 2

Excel: Insert formulas in a dynamic range

Answers (4)

Related Questions