Problem extracting information from the list

[ElaineMok]

Hi can anyone here kind enough to tell me what is wrong with my formula.
I want to extract the list of suppliers from another worksheet, but the last row of information is always missing!
And how to delete the empty rows from the drop down list. Pl help!
Thanks!

[JohnTopley]

Try

=IFERROR(INDEX(POS!$E$2:$E$100,SMALL(IF(POS!$E$2:$E$100<>"",ROW(POS!$E$2:$E$100)-ROW(POS!$E$2)+1,""),ROWS($1:1))),"")

You get a problem because you have blanks in your supplier list so the COUNTA used to determine the length of the named range is wrong.

[ElaineMok]

yes, i know is the blanks that is causing the problem. May I know what is the problem with the count on the suppliers? I keep having one less. Why is that so?

[JohnTopley]

COUNTA in your named range gives a count of 7 and in the INDEX formula uses 7 as the number of rows which takes us to row 8. But your data ends in row 10: hence missing data.

[ElaineMok]

This formula does not give me a unique distinct list.