Add rows to table produced with filter function based on additional argument

Question

This is an extension to a question I previously asked and had answered Populate rows on different sheet based on cell value on other sheet

I have a master spreadsheet which contains all subjects e.g.

If value in column D is 'TRUE' I wish to populate row on second sheet with the first three columns (cells in red are copied through, cells in black manually entered.

The solution given in the original question works perfectly, i.e. =FILTER(A2:C4,D2:D4=TRUE)

I would now like to enter additional rows to the second sheet, based on the value in column F. If column F is False, I would like an additional row with the first three columns repeated.

This would continue to repeat if 'FALSE' was entered again in column F.

Ideally this would be automated (e.g. by value in column F or similar), but if this is not possible, is there a way to achieve this manually whilst keeping the original FILTER function working?

Answer 1

To avoid your new inputs (column F) modifying your first array result, I would recommend using an intermediate sheet. So:

• Sheet1: original data
• Sheet2: column headers, plus =FILTER(A2:C4,D2:D4=TRUE) in A2 (as above)
• Sheet3: new sheet for 'dynamic' entry with rows adding automatically (as below)

In Sheet3, add column headers as above in row A. Additionally, add an 'Offset' column (column G) and a single cell in (say) J1 which is the total row count of your first result. One simple method:

• J1: =COUNTA(Sheet2!A:A) - 1 (-1 to exclude headers)

Additionally, set G2 (i.e. the first 'Offset' cell) to 1.

Now configure row 3 as follows (we use row 3 so you can copy 'up' to row 2, except for the 'Offset' column, as well as down to the maximum plausible extent of your range):

• A3: =IF($G3 = "", "", OFFSET(Sheet2!A$1, $G3, 0))
• B3: =IF($G3 = "", "", OFFSET(Sheet2!B$1, $G3, 0))
• C3: =IF($G3 = "", "", OFFSET(Sheet2!C$1, $G3, 0))

(You can copy A3 across to B3 and C3, because of the relative row references.)

• D3-F3: Manual entry
• G3: =IF(G2="", "", IF(AND(G2=$J$1, OR(F2 = "", F2)), "", G2 + (IF(F2 = "", 1, IF(F2, 1, 0)))))

Now, as stated, copy row 3 down as far as needed, and A3:C3 up to A2:C2 (but leave G2 hard-coded to 1).

You should find that by entering TRUE or FALSE in column F, the range resizes as you require.

You must still work downwards, otherwise any previously entered values in columns D and E (below your current working cell) will become misaligned.

Here is an illustration of Sheet3 to help:

Here is an animated GIF to illustrate:

And here is a link to a (read-only) Google Sheet which illustrates the behaviour.

You might of course need to adapt this for your needs by adding error handling etc.