Hi!

If I can understand what it is that rules this, I can manage it.

Actually, this is a very simple formula once you understand how it works.

The problem you encountered is the one most people have with this type of
formula.

The INDEX function is used to hold an array of values. In this case that
array is A10:A999. This array holds a total of 990 values. Even though the
physical location of this array is A10:A999, the "virtual array" that is
being held in the INDEX function starts with position 1 through position
990. Whe

A10 is in the 1st position
A11 is in the 2nd position
A12 is in the 3rd position
....
A999 is in the 990th position

Now, this is where the ROW($1:$999) function comes into play and is where
people usually make their mistakes. The argument to the ROW( ) function
*MUST MATCH EXACTLY* the SIZE of the array being held in the INDEX function.
In this case that would be: ROW($1:$990).

The logical test in the IF function returns the corresponding value from the
ROW($1:$990) function and that value in turn returns the corresponding value
from the INDEX array.

Biff

"sonar" wrote in
message ...

Hi Biff

Thanks for the help, my only problem now is, is that I get a #Num error
from row 11 onwards when I want to try and change the range from 10:999,
why is that?

(=IF(ROWS($1:2)<=COUNT('3DBC'!$V$10:$V$999),INDEX( '3DBC'!A$10:A$999,
SMALL(IF('3DBC'!$V$10:$V$999<"",ROW($1:$999)),ROW (2:2))),"")}

If I can understand what it is that rules this, I can manage it.

Regards
Sonar


--
sonar
------------------------------------------------------------------------
sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=399751