Dynamic top 20 list

[Xhawk57]

I have a running list of orders where as each new item is ordered it is added
to the bottom of the list (in column A is the date it was ordered, column B
is the product, etc.).

I would like to create a list in a separate workbook that shows only the
last 20 products ordered by date (column A), and that updates automatically
every time a new order is added to the "master list".

I have started to experiment with a dynamic list using the
=offset(...(counta()) function to define the range, but I am stuck on how I
would display it in a new workbook and automatically update the new list.

Any ideas/help is greatly appreciated.

Thanks in advance.

 

[Gary''s Student]

Do you want the first item in the top20 list to be the most recently ordered
and then work backwards from there??

 

[Xhawk57]

it does not have to be, but it would be ok if it was. As long as the last 20
items appear, they can be in either assending or decending order.

Thanks.

 

[Gary''s Student]

O.K. This assumes that because entries are made at the bottom of the list,
the most recent data is always at the bottom. Say the data is in cols A & B.

We are going to "invert" the two columns and display the 20 "top" items in
the "inverted" list. First we need a "helper" cell, say G1. In G1:

=SUMPRODUCT(MAX((ROW(A1:A65535))*(A1:A65535<>"")))+COUNTA(A65536)

G1 will display the row number of the last cell in column A with data

Then in C1 enter:

=INDEX(A:A,G$1+1-ROW()) and copy down thru C20

In D1 enter:

=INDEX(B:B,G$1+1-ROW()) and copy down thru D20



Cols C & D will display the most recent data in cols A & B

 

[Xhawk57]

That worked great!!! Thank you!

O.K. This assumes that because entries are made at the bottom of the list,
the most recent data is always at the bottom. Say the data is in cols A & B.

We are going to "invert" the two columns and display the 20 "top" items in
the "inverted" list. First we need a "helper" cell, say G1. In G1:

=SUMPRODUCT(MAX((ROW(A1:A65535))*(A1:A65535<>"")))+COUNTA(A65536)

G1 will display the row number of the last cell in column A with data

Then in C1 enter:

=INDEX(A:A,G$1+1-ROW()) and copy down thru C20

In D1 enter:

=INDEX(B:B,G$1+1-ROW()) and copy down thru D20



Cols C & D will display the most recent data in cols A & B