Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic top 20 list
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic top 20 list
Do you want the first item in the top20 list to be the most recently ordered
and then work backwards from there?? -- Gary''s Student - gsnu200812 "Xhawk57" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic top 20 list
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" wrote: Do you want the first item in the top20 list to be the most recently ordered and then work backwards from there?? -- Gary''s Student - gsnu200812 "Xhawk57" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic top 20 list
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 -- Gary''s Student - gsnu200812 "Xhawk57" wrote: 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" wrote: Do you want the first item in the top20 list to be the most recently ordered and then work backwards from there?? -- Gary''s Student - gsnu200812 "Xhawk57" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic top 20 list
That worked great!!! Thank you!
"Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200812 "Xhawk57" wrote: 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" wrote: Do you want the first item in the top20 list to be the most recently ordered and then work backwards from there?? -- Gary''s Student - gsnu200812 "Xhawk57" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic PivotTable List | Excel Discussion (Misc queries) | |||
Dynamic List | Excel Worksheet Functions | |||
Dynamic territory list | Excel Worksheet Functions | |||
Changing named Validation list to Dynamic list. | Excel Discussion (Misc queries) | |||
Making a dynamic list?! | Excel Discussion (Misc queries) |