How to always use top 3 rows (excluding header) in a formula

"Is there a way to achieve what I'm looking for?"

Here is one method, using the OFFSET function.

The example below was constructed with Numbers for Mac.

Start by using a separate table for the formula. Table 1 in the example is the Data table. The formula(s) collecting data from the first three (non-header) rows is Table 1-1.

The formula shown below the tables is entered as shown in cell A2 of Table 1-1.

From there, the formula is filled right to column 3, then filled down to row 4

On inserting a new row above Row 2 the results on Table 1-1 change as shown below.

As data is added to the new row of Table 1, the formulas on table 1-1 copy them into the rows 2, 3 and 4 of

Table 1-1.

Regards,

Barry

The best method to use to refer to those three rows depends on what your formula needs.

OFFSET can reference a single cell or a range of cells as long as you have an anchor cell (such as B1) that stays put.

OFFSET(Table 1::B1,1,0) is cell B2

OFFSET(Table 1::B1,1,0,3,1) is B2:B4

INDEX can be used to reference a single cell.

INDEX(Table 1::B,2) is cell B2

INDIRECT can reference a single cell or a range of cells.

INDIRECT("Table 1::B2")

INDIRECT("Table 1::B2:B4")

INDIRECT is probably the easiest to understand but if you change the table name or add a column to the left of B, you will have to fix the formula because the address is hardcoded as text. The others will automatically adjust, just like regular cell references.

dang42 wrote:

The table will eventually be quite long and as I'll be entering this data on my iPhone I'd prefer to add a new row at the top of the table (row 2, below header) rather than always scrolling to the bottom of the table.

Note that Numbers iOS has built-in "forms" just for this type of situation, so you don't have to scroll to the bottom or struggle with a workaround using formulas.

Setting up a Form is simple.

Make sure the first row of your data table is a Header Row.

Then tap somewhere in your data table, then the + at the upper left, choose 'New Form', and link to the data table.

Then, to enter new data, just go to the Form instead of the table. The Form will remember the bottom of the table. No scrolling needed.

If you are bringing values in from another app instead of entering them directly then keep in mind that the Shortcuts App also offers actions that will 'Add Record to Form' or 'Add Row to Top or Bottom of Table'.

SG