Displaying last 10 entries

Posted by Michael on March 11, 2001 7:26 AM

Hi and thanks in advance for help on this. I have a list of data that is listed by date. The dates are not continuous, they may skip a day, week, or even a month. They are listed in cells A2 on down to A100. I want to make a smaller list to print out that only lists the last 10 entries. I am trying to do this with a workbook function but am not having any luck. Anyone have any ideas on how to do this?

Posted by Aladin Akyurek on March 11, 2001 8:09 AM

Here a formula-based solution.

Assuming your entries are in A from A2 on.

If A1 is blank,

In B2 enter: =counta(A:A)+1

If A1 contains a label, remove +1 from the above formula. If A1 is blank, keep the formula as is.

In B3 enter: 10 (this is your criterion of last 10 entries)

In C2 enter: =IF(ROW()-1&LT;=$B$3,INDIRECT(ADDRESS($B$2-$B$3+ROW()-1,COLUMN($A$2:$A$100))),"")

Copy down the formula in C2 as far as needed.

Aladin

Posted by Celia on March 11, 2001 4:05 PM

Michael
It is probably better to do this with a macro.
Try this :-

Sub PrintLast10()
With Range(Range("A2"), Range("A65536").End(xlUp).Offset(-10, 0)).EntireRow
.Hidden = True
ActiveSheet.PrintOut
.Hidden = False
End With
End Sub

Celia

Posted by David Hawley on March 11, 2001 4:19 PM

Hi Micheal

Another method would be to use a Dynamic Named Range. If you are not sure how to do this then follow my link to my web site and click the "Dynamic Named Ranges" link.

Lets say you call your dynamic range "MyDates" you could then use this formula starting from Row 2(first row for heading) in any Column on any sheet.

=INDEX(MyDates,ROWS(MyDates)+2-ROW())

Copy this down to Row 11.

Dave

OzGrid Business Applications

Posted by Michael on March 11, 2001 4:21 PM

That works perfectly! Although I had to change the 10 in cell B3 to a 12 in order for it to list the last 10 entries. For some reason it would only list the last 8 otherwise.

If I could ask one more favor. It lists the dates perfectly from column A, but, is there a way for it to list everything from column A all the way to the last cell on the right, column H?

Thanks again, this already has been a tremendous help.