1

In one sheet A I have an ongoing number of rows where the first column is a timestamp in ascending order (more recent entries at the bottom).

I'd like to have another sheet B where only the ten most recent entries (per timestamp) appear.

Is there a function that can achieve that?

If using the timestamp is not feasible, then the ten bottom rows, given that those will be the most recent.

  • Perhaps you can share a doc with us and show the expected outcome. – Jacob Jan Tuinstra Mar 20 '13 at 10:54
2

This is another way of doing it.

Formula

=QUERY(TIMESTAMPS!A:B, "SELECT A, B ORDER BY A DESC LIMIT 10")

Explained

The QUERY formula needs the data from the timestamps sheet, for it to process. The result must contain column A and B, of which column A is sorted descendingly (new to old). Finally, the result is limited to 10 entries (rows).

Screenshot

enter image description here

Example

I've created an example file for you: how to show the 10 most recent rows

1

If you want the results in rows1:10 please try:

=large('A'!$A:$A,11-row())  

in Row1 and copy down to Row10. If in reverse order leave out 11-. If in some other row either change 11-row() to a number to represent which of the 10 belong in that row (1 being the most recent, 2 the next most recent, etc) so each formula is individually tailored, or use an offset in the formula and then copy, so for the results to show starting in Row7 and continuing downwards use 17-row() instead of 11-row().

1

Since other answer appears not to suit, one adapted from Tim Williams:

=OFFSET(A1,COUNTA(A:A)-10,0,10,1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.