Best way to filter by formula and add rows?

I don't have a solution in mind but you are correct that the formula will not fill into new rows if the formula is part of the filter. Numbers will replace it with the "last calculated value", which will be the value from a row that is visible. This ensures the newly added row will be visible. A formula in the newly added row could calculate to something that hides the row as soon as you make it so it won't put the formula, just a value.

.

papalapapp wrote:

most formulas will not populate in new rows.3

The formulas in a Filter column populate here in new rows added at the bottom of the table.

Your animated screenshot seems to show you doing something (not quite sure what) in the middle of the table.

SG

The rules for when/why Formulas are copied down into new rows are a little nuanced. I'll try to explain:

For any inserted row, the cell values will populate with formulas provided:

1. The cell in the current row contains a formula (static values are not carried into the new row)

2. The cell in the current row ± 1 contains the same formula, thus creating a sequence.

Take the following table as an example:

Column A is filled with the formula =ROW() which simply inserts the current row number.

Column B is filled with static numbers, i.e. not generated by a formula, but typed directly.

Column C has random rows (including the last row) that uses the same =ROW() formula as column A, but not sequentially.

Inserting a new row at the bottom, after row 10, and following the above rules, you end up with:

Following the rules, you can see that the formula in A10 has been copied forwards, because it's a formula, and it forms a sequence with the preceding cell A9

Cell B11 is blank, because B10 contains a static value (static values are not copied into the new row)

Cell C11 is blank, because while C10 does have a formula (the same formula as A10), it doesn't form a sequence with C9 and therefore not carried forwards.

Hope that helps.

papalapapp wrote:

when a filter is applied to that column which contains a formula.

Finally realize implication of "when a filter is applied." Means when the Filters switch is turned on.

I've never run into that. I simply turn off the filter before adding new values (since it's so easy). Then if I still need the filter I just turn it back on again.

Another thing you could consider is to add Categories to the Filter column. When you add a row the formula is filled down even when you have Categories turned on. (The date doesn't fill automatically, though.).

SG

You can do something like this:

In D2, filled down:

=AND(A2>=Table A::$B$2,A2<=Table A::$B$3)

In general is not a good idea to have blank rows in a table the way you might in an Excel worksheet.

As you add rows to the table the formula in the Filter column will be inserted automatically in the new rows.

SG

papalapapp wrote:

That formula makes sense but it won't be inserted automatically when I add new rows while the filter is active.

On my machine when filter is active and I add new rows the formula is inserted automatically. Couldn't be simpler.

SG