Apple Numbers: On a separate sheet, list 20 most recent entries from main database (dynamic update)

If you only ever add just one record per date then you can do something like this:

In D2 of the "most recently added" table, filled down:

=LARGE('Data -1'::D,ROW()−1)

In A2, filled right to C2, and then down:

=LARGE('Data -1'::D,ROW()−1)

If you may add more than one record on the same date then you could add "index" columns, which you could then hide:

In column E2 of the data table, filled down:

=D2+ROW()

In column E2 of the recently added, filled down:

=LARGE('Data - 2'::$E,ROW()−1)

In column A2, filled right to D2, and down:

=XLOOKUP($E2,'Data - 2'::$E,'Data - 2'::A)

You can try this, but in the end you may find the powerful built-in sorting and filtering features in Numbers are easier!

SG

Your formula in Y2 refers correctly to $Y, an entire column

Your formula in Y3 refers to Y3, a cell instead of an entire column.

So something went wrong.

Once you have the correct formula in Y2, you need to "Autofill" that down the column. (I have 21 rows in my example table when including the Header Row).

On the iPad

1. tap the cell Y2 and the Cell actions button lower right
2. tap 'Autofill Cells' and drag the bottom edge of the yellow rectangle down the column.

The result is in descending date order.

SG

The error message suggests that you don't have a value in X2 the Numbers recognizes as date & time. A date & time value value would be right-aligned by default.

Where are you getting that format? Are you importing it or entering it manually? If entering it you could consider using - instead of . between the year month and day.

You can convert to "true" date & time with something like this:

=SUBSTITUTE(A2,".","-")+0

Or if your region uses , as the decimal separator:

=SUBSTITUTE(A2;".";"-")+0

Adding the 0 coerces the result of SUBSTITUTE from Text to Date & Time

If you are referring to X2 you would of course use X2 in the formula instead of A2.

SG

In the text you say you use formula

=LARGE('Table 1'::$Y,ROW()−1)

But in your screen capture we see that $Y has been replaced by Y3.

Use $Y in cell Y2, then grab the yellow dot at the bottom of the cell to drag-copy the formula down until the 20th item.

Jonathan Christensen wrote:

I then took a step back and got the exact functionality I wanted using a pivot table, and a couple of filtering options.

Yes, that is my experience. Pivot Tables (and Filters) are powerful, flexible, and easy to use. They can prevent hours of fiddling with formulas and extra columns and such. The only drawback: don't forget to "refresh" after adding or changing data in the source table! One click on the refresh icon or a trip to the menu will do that.

SG