I am not very good in explaining, but I will try.

In the sample that I gave you, I had:

INDEX('Stock Req 13 BarCodes'!$A$1:$W$999,SUMPRODUCT(SMALL(ROW('Stock
Req 13 BarCodes'!$10:$999)*('Stock Req 13
BarCodes'!$V$10:$V$999<""),ROW()-8+COUNTA('Stock Req 13
BarCodes'!$V$10:$V$999)-COUNTIF('Stock Req 13
BarCodes'!$V$10:$V$999,"0"))),COLUMN()-1)

SUMPRODUCT() will generate an array that contains 990 values:
{0 0 0 0...0 0 0 10 11 12 13 14 15 25 30}

As you can tell, SUMPRODUCT() returns all the row numbers where 'Stock
Req 13 BarCodes'!V10:V999 not equal "". In your case it will return
all the row numbers where V10:V999 are numbers. At position 983, we
have a value of 10 which is the row number that a number first appears
in V10:V999. Position 984 contains the next and so on.

Here I used SMALL() to output these values. It looks something like:

SMALL({0 0 0 0...0 0 0 10 11 12 13 14 15 25 30},ROW()-8+COUNTA('Stock
Req 13 BarCodes'!$V$10:$V$999)-COUNTIF('Stock Req 13
BarCodes'!$V$10:$V$999,"0"))

COUNTA() will return 990 which is your total array length.
COUNTIF() will return a number(8 in this case) which is number of
values that are greater than 0.

Therefore COUNTA()-COUNTIF() will always point at the position of the
last 0 in the array. However what you want is everything that comes
after that. Since the first line in your sheet SHORT-1 is row 9, using
ROW()-8 will generate 1,2,3,4,etc as you drag the formula down.

Now everything is simple, the functions above return a row number which
is used in INDEX():

INDEX(range, row number from above functions, column number)


Hope that helps.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=398757