last 10 numbers in a row

[det1nrs]

I have rows of numbers that gets a new number added to the right most column every week. I need a formula that will return the LAST 10 numbers of those rows automatically to another worksheet in the workbook. The kicker is that sometimes there isn't a number added for some of the rows. In that case the cell is left blank. I'm using Excel 2007. Please help!!

 

[venkat1926]

use autofilter
click the filter arrow of the column
click top 10
in the wizard choose
bottom - 10

copy filtered data somewhere else and remove filter form the original column

 

[Peter_SSs]

use autofilter
click the filter arrow of the column
click top 10
in the wizard choose
bottom - 10

copy filtered data somewhere else and remove filter form the original column

venkat

I believe that the OP wants the last 10 numbers in a ROW. So
a) AutoFilter will not be any good because it works on columns and
b) In any case the AutoFilter Bottom 10 would return the 10 smallest numbers, not the last 10 numbers in the column.


det1nrs
Can you confirm that if the row was
1 2 3 4 5 b 6 b 7 8 9 b 10
where b represents a blank, then you still would want
1 2 3 4 5 6 7 8 9 10
returned rather than
4 5 b 6 b 7 8 9 b 10?

 

[Domenic]

Assumptions:

1) Sheet1 contains the data

2) Row 2 is the first row containing data

3) Each row begins at Column B

4) Only non-blank cells are to be returned

Formula:

On Sheet2...

B2, copied across and down:

=IF(COLUMNS($B2:B2)<=COUNT(Sheet1!$B2:$IV2),INDEX(Sheet1!$B2:$IV2,LARGE(IF(Sheet1!$B2:$IV2<>"",COLUMN(Sheet1!$B2:$IV2)-COLUMN(Sheet1!$B2)+1),MIN(10,COUNT(Sheet1!$B2:$IV2))-COLUMN()+COLUMN($B2))),"")

...confirmed with CONTROL+SHIFT+ENTER. Note, however, since you're using Excel 2007, convert the data into a list...

Data > Create > Create List

Now the ranges will automatically adjust as data is added or removed.

Hope this helps!