selecting data from the last three entries

Posted by john neeve on December 28, 2001 3:57 AM

Can anyone help? I have made a small spreadsheet which helps with record keeping for a race game. I input the finishing position of each contestant each time they race & I need a function that looks along a row of data and selects the last three results.
Thanks in anticipation.

Posted by Joe Was on December 28, 2001 5:53 AM

We need a sample!
Does your finishing position attach to a date or or other identifier? Help us ou here?

Or, do you just want the last three columns of data moved to a new sheet? JSW

Posted by Tom Urtis on December 28, 2001 6:03 AM

Here's a possible option

If I read your question correctly, you want to select the last 3 rows of data in a spreadsheet to which you are often adding data. This code will do that, assuming your sheet has data in columns A:D (modify as needed), and that each cell in the first and last column (in this example A and D) is populated. If this is not what you need please clarify.

Sub SelectLastThree()
Dim RStart As Range
Dim REnd As Range
Set RStart = Sheets("Sheet1").Range("A65536").End(xlUp)
Set REnd = Sheets("Sheet1").Range("D65536").End(xlUp).Offset(-2, 0)
Range(RStart, REnd).EntireRow.Select
Set RStart = Nothing
Set REnd = Nothing
End Sub

HTH

Tom Urtis

Posted by john neeve on December 30, 2001 1:47 AM

Re: Here's a possible option

Tom, Thanks, but I am a real novice. Is this visual basic and if so how do I enter it? I would like to attach a sample but don't know how to do that.

Thanks
John

Posted by Aladin Akyurek on January 01, 2002 1:36 PM

A guess... (Re: Here's a possible option)

I didn't try out Tom's VBA solution.

As I understand your question, you're entering scores in a row, say in row 2, and you want to sum last 3 entries. If so, the formula

=SUM(OFFSET(A2,,MATCH(9.99999999999999E+307,2:2)-3,,3))

will give you the sum of the last 3 entries.

Caveat. There should not be anything else but scores in row 2 and this formula should be entered in a cell in a row which is not used for entering scores.

Aladin

==========